How do I get a form button to run FIND from the command bar?

[t.powley]

I would like to be able to create a form button that will open the FIND option (CTRL+F) in the command bar.

Is there an easy way to get a form button to do this?

Thankyou,
Tom

 

[Cbrine]

Why don't you enter the customize menu, and actually select the Excel Find button, and add it to your commaned bar. I could give you code, but that will never work as well as the native stuff.

HTH
Cal

 

[t.powley]

I would do but the spreadsheet is used by many people and they do not have the find button setup in this way.

Most of people who will use the spreadsheet wont know about pressing CTRL+F or that there is a Find button located in the command menu.

Basicly the spreadsheet contains colums that contain:
Phone numbers, Class, Departments, Teachers Names

The find command will seach all of these very easily.

 

[Cbrine]

Add the following code to the thisWorkbook level of the VBE. It will add a custom toolbar and button for FIND to excel, as well as remove it when it's closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As CommandBar

On Error GoTo ErrorHandler

Set c = Application.CommandBars("Find")
c.Delete
Exit Sub

ErrorHandler:
ErrorHandler

End Sub

Private Sub Workbook_Open()
Dim b As CommandBarButton, c As CommandBar

On Error GoTo ErrorHandler

'Add SheetSelector Button Bar
Set c = Application.CommandBars.Add("Find", , , True)

'Add Button to new buttonbar, adjust attributes
Set b = c.Controls.Add(msoControlButton, , , , True)
b.Style = msoButtonCaption
b.Caption = "Find"
b.OnAction = "FindData"
c.Visible = True
c.Position = msoBarTop

Exit Sub

ErrorHandler:
ErrorHandler
End Sub
Sub ErrorHandler()

Select Case Err.Number
Case 5
    End
Case Else
    MsgBox Err.Number & " " & Err.Description
End Select
End Sub

Add this code to a new module. It will open the find dialog.

Sub FindData()
Application.Dialogs(xlDialogFormulaFind).Show xlValues
End Sub

HTH
Cal

 

[t.powley]

That is fantasic and works perfectly, Just what I need.

Thankyou so much,

Tom

 

[erik.van.geit]

Sub FindData()
Application.Dialogs(xlDialogFormulaFind).Show xlValues
End Sub

I was browsing to find some documentation and found out that this code is not correct.
The intent of "xlValues" was clearly to search in the values, but the first argument of that dialogbox is "the text to find"
The way it is coded here will not bug, but will display "-4163" in the dialogbox, which is the value for "xlValues".

The code should be

Application.Dialogs(xlDialogFormulaFind).Show "", 2

other arguments can be added.

kind regards,
Erik