Ok, one approach:
Open the excel file in question.
Open the
VB editor (Alt F11 or similar).
There, menu Insert Module.
You will see a new blank module sheet. Paste this into it:
' ****** beginning of block *****
Option Explicit
Sub ButtonClick()
Dim S As String
Dim R1 As Range, R As Range
Dim Cbo As DropDown
S = ActiveSheet.Range("A1").Value
If S = "" Then Exit Sub
Set Cbo = ActiveSheet.DropDowns(1)
Cbo.RemoveAllItems
On Error Resume Next
Set R = Cells.Find(What:=S, _
After:=Range("A1"), _
LookAt:=xlWhole)
If R Is Nothing Then Exit Sub
Cbo.AddItem R.Address(False, False)
Do
Set R = Cells.Find(What:=S, _
After:=R, _
LookAt:=xlWhole)
Cbo.AddItem R.Address(False, False)
Loop Until R.Address = "$A$1"
Set Cbo = Nothing
End Sub
Sub CboSelect()
Dim Cbo As DropDown
Set Cbo = ActiveSheet.DropDowns(1)
ActiveCell.Activate
Range(Cbo.List(Cbo.ListIndex)).Select
Set Cbo = Nothing
End Sub
' ****** end of block *****
Now return to Excel and your sheet.
Go menu View Toolbars, select the Forms Toolbar.
There is a button on it. Place a button over cell B1.
Assign the "ButtonClick" macro to it if asked. If not then do it manually by
rightclicking it and choose "assign macro"
There is also a ComboBox on it. Place it below the button. Assign the
"CboSelect" macro to it if asked. If not then do it manually by
rightclicking it and choose "assign macro".
And that's it. Enter something in A1, click the button and matching cells
are listed in the combobox. Select a cell address in the combobox and you're
transported to it on a magic carpet.
Change the text xlWhole to xlPart in the macro code if you want to search
for part of the cell content instead of whole cells.
HTH. Best wishes Harald
"Natalie" skrev i melding
...
Ideally I would like -
Cell A1 - Type in search critieria
Cell B1 - Search button to click after typing in criteria
Then
Any name matching the criteria to appear in the rows below?
Thanks!
"Harald Staff" wrote:
Sure. How would you like it to behave ? Where shall the user enter her
search criteria ? How should the button respond to it ?
HTH. best wishes Harald
"Natalie" skrev i melding
...
Hi,
Is there any way that I can create a "find" button on my excel
spreadsheet?
I have a lot of data for people that aren't familar with excel and I
thought
adding a button with a macro on the actual worksheet would make it
easier?