Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search and select using a Userform

    Excel 97: I'm a novice writer of macros/visual basic. I have created a userform that will allow the user to enter in the part number, quote number, etc. that they want to find(8 columns of options, undetermined number of rows) . I then have an OK button which would launch the search or a CANCEL button to exit the search. After performing the search, I would like the cell that the item is located in to be the active cell. What's the easiest way to do this? Also, is there a way that I can use a "Find Next" button to find the next occurence of the item? (A macro that would open the Find option on the Edit menu would work for me if there's a way to do that.)

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: search and select using a Userform

    If you insert the following line into a macro it should bring up the standard XL dialog box for Edit Find :

    Application.Dialogs(xlDialogFormulaFind).Show

    Hope that is of help to you

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and select using a Userform

    That worked. Thanks.

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and select using a Userform

    Correction. This opens the Find form, but it won't actually find anything within the spreadsheet. I added a line to the macro to make sure that it was looking in the correct sheet, but it didn't make a difference. When I do a Find using Ctrl+F that works.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a Userfo

    Does your code select the search target range first?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and select using a Userfo

    No it doesn't. Do I need to specify a range when opening Find through a Macro?

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a User

    I think you are correct, it doesn't. But you do have to activate the sheet being searched. Perhaps you could post the relevant code sniplet?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and select using a User

    Here is the macro. The name of the sheet that I want to do the Find on is "XRef".

    Sub FindCustomer()
    ' keyboard shortcut: Ctrl+Shift+C

    Sheets("XRef").Select
    Application.Dialogs(xlDialogFormulaFind).Show

    End Sub

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a User

    This works, but it would be nice to exactly emulate the way Excel's normal find works without the selection color inversion:

    Sub FindCustomer()
    ' keyboard shortcut: Ctrl+Shift+C
    Sheets("XRef").Select
    Cells.Select
    If Application.Dialogs(xlDialogFormulaFind).Show Then ActiveCell.Select
    End Sub

    Andrew can probably help us, but as I post this it's near his bedtime.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: search and select using a User

    John, it's past my bedtime and most likely sell by date, but I should have mentioned that in using the dialog, it is best to have a range selected. You seem to have discovered that with cells.select, but that selects the entiure worksheet. If you know the range you want to search, select it and use your very good If Application.Dialogs(xlDialogFormulaFind).Show Then ActiveCell.Select. Incidentally xlDialogFormulaFind can be replace by 64.

    Good night now

    Andrew

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a User

    Good night, Gracie.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search and select using a User

    Thanks. It works!

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a User

    As Andrew notes, "Cells.Select" selects the entire sheet. If customer names are only in column B, use "Columns("B:B").Select" instead, or if in columns B-D use "Columns("B").Select" or if in row 7 use use "Rows("7:7").Select", etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: search and select using a User

    Hi John,
    Unfortunately, the only arguments you can pass to the Show method for that dialog are the options that actually appear on the Find dialog (e.g. LookIn, Match Case) and the Range to look in isn't one of them. An alternative would be to use Cells.Find (which I think is what the Find dialog effectively does) with an InputBox to determine what to find. You can then have another macro with Cells.FindNext if needed.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search and select using a User

    Rory, help me understand what you are saying. Within the above in XL97 when I tested:
    Columns("B:B").Select
    If Application.Dialogs(xlDialogFormulaFind).Show Then ActiveCell.Select
    the find was restricted to the preselected range, and clicking Find Next in the dialog or hitting Enter cycled through the true finds in that column.

    I follow you that in the situation where the coder wants the user to select the Find target range, the code will need an InputBox to select it, but in this example the code preselects the CustomerName range.

    What am I missing?
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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