search and select using a Userform

Topic

New Reply

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.)

Reply | Quote

Replies

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

Reply | Quote

That worked. Thanks.

Reply | Quote

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.

Reply | Quote

Does your code select the search target range first?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Good night, Gracie.

Reply | Quote

Thanks. It works!

Reply | Quote

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:D”).Select” or if in row 7 use use “Rows(“7:7″).Select”, etc.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Sorry John, I don’t think I was very clear – the inputbox was to enter the text to find not the range to look in. I was referring to your comment about avoiding selecting the cells first – the only way to do that is to use the Find method rather than the dialog. It’s a little annoying that you can pass arguments to the
Dialogs(xlDialogFormulaFind).Show
for just about everything except the range to look in!
I hope that makes more sense?

Reply | Quote

Yes, now I understand. It would have made immediate sense if I was a instead of a , as I would have known which post you were responding to. Sigh.

Reply | Quote

Join the club – I think I’m in danger of wearing out the wheel on my mouse from scrolling up and down trying to work out who’s talking about what and to whom!!

Reply | Quote

Hi,

Maybe the attached utility is of any help?

Reply | Quote