Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search via Userform (97 SR2)

    I have a method of doing this, but it's not very effective, my question is, what methods have some of you employed to search a given range from a userform? Is there a way to make a find style box pop up via VBA? Navigation on my form is based on the row number, which is stored as "i" in my project. Ideally, the find box would pop up, user would type in thier criteria, and form would navigate to 'i', which would equal the found result row.

    Any tips/suggestions/ideas/solutions?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Search via Userform (97 SR2)

    Hi DrkRealm

    Its not clear what you are trying to do from your post. Sorry <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    You want to have a userform display so that the User can type something to be found in a sheet? How about using the Built-in Find dialog?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search via Userform (97 SR2)

    The problem with the built in find dialog is that when the userform is open, it can't be displayed.

    Ideally, I would have the find dialog appear, but instead of returning the actual result, it would instead return the row number...

    More or less, i'm just looking for ideas... this whole project came about because the users can't reliably use a spreadsheet, so we've got a glorified userform to put the data into a spreadsheet for them... <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Search via Userform (97 SR2)

    Hi DrkRealm

    Now it is clearer. I understand that you have a userform displayed for the user to interact with, and then you want to find an item on the worksheet and return the row where it happens to be.

    I had done something like that, where I had a UF with Muti-page Control hidden, when the User clicks the find button it changes pages and display my custom find page with a bunch of controls.

    So the User thinks that he got a new form to use the find function, but in fact its a new page on that same form he had before.

    To get the row of the item found is easy you could do something like:

    Dim lRowWhereFound As Long '/Holds the Row where the Item was found.

    With ActiveSheet
    lRowWhereFound = .Cells.Find(What:="1122").Row
    End With

    You can grab the 1122 from what the user has entered, and if you don't find it, you can control what happens.

    I hope this is a starting point.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search via Userform (97 SR2)

    Drk,

    See attached, I whipped it up in about ten minutes, a new record for me! I hope this is what you had in mind, and that it gives you some ideas.

    It is a workbook with a form and some sample data in it. Got to the VB editor, run the form. Type something like GHI into the "Find What?" text box. Type A1:A10 into the "In This Range" box. Click Find button. The row will appear in the "It's in row..." text box. The actual finding and reporting of results is done with one line of code.

    The only "unusual" thing I did was use a control called a RefEdit, which allows you to specify the search range by selecting in the spreadsheet itself. I think this is a standard control with Excel 2000, but I'm not sure.
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Search via Userform (97 SR2)

    You got a solution from JIMbythebay...

    Hope you can get started...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search via Userform (97 SR2)

    I already have some pages on the form.. perhaps I can work it with that..

    Thanks for the feedback, We'll see what happens!

    Warmest Regards,
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search via Userform (97 SR2)

    Very nice! I think i'll incorporate it with Wassim's suggestions, should make for an effective search function.

    thanks millions!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •