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

t.powley

Board Regular
Joined
Jun 13, 2002
Messages
76
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Board Regular
Joined
Jun 13, 2002
Messages
76
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.

Code:
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.

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

HTH
Cal
 

t.powley

Board Regular
Joined
Jun 13, 2002
Messages
76
That is fantasic and works perfectly, Just what I need.

Thankyou so much,

Tom
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Code:
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
Code:
Application.Dialogs(xlDialogFormulaFind).Show "", 2
other arguments can be added.

kind regards,
Erik
 

Forum statistics

Threads
1,141,023
Messages
5,703,801
Members
421,318
Latest member
cg_cartoonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top