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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,990
Messages
5,545,370
Members
410,679
Latest member
rolandbianco
Top