Assigning a macro to a "button"

Y

Yvon

Hello,


I have a big spreadsheet with a database and want to make the search
feature (CNTRL F)user friendly by adding a "button" to enable this.
Unfortunately when creating the macro you cannot stop recording unless
you enter data into the search or either close it which obviously
renders the "button" useless. I am not familiar with VBA and therefore
must do so through the macro recording. Any ideas???
 
D

David

Yvon wrote
Hello,


I have a big spreadsheet with a database and want to make the search
feature (CNTRL F)user friendly by adding a "button" to enable this.
Unfortunately when creating the macro you cannot stop recording unless
you enter data into the search or either close it which obviously
renders the "button" useless. I am not familiar with VBA and therefore
must do so through the macro recording. Any ideas???
Click to expand...

A quick Google search turned up this from Tom Ogilvy:
-------
Tom Ogilvy Jan 13 2000, 12:00 am show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "Tom Ogilvy" <[email protected]> - Find messages by this author
Date: 2000/01/13
Subject: Re: 2 questions
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Put this macro in a general module in the VBE


Sub Button1_click()
Application.Dialogs(xlDialogFormulaFind).Show
End Sub


Get a button from the forms toolbar and put it on the sheet. Right click
on
it and choose assign macro. Assign the above macro.
 
D

Dave Peterson

There's a builtin button that you can add (if it's not there already).

Tools|Customize|commands tab|Edit category

Look for the binoculars icon.
Drag it to your favorite toolbar.
 
Y

Yvon

David said:
Yvon wrote


A quick Google search turned up this from Tom Ogilvy:
-------
Tom Ogilvy Jan 13 2000, 12:00 am show options

Newsgroups: microsoft.public.excel.worksheet.functions
From: "Tom Ogilvy" <[email protected]> - Find messages by this author
Date: 2000/01/13
Subject: Re: 2 questions
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse

Put this macro in a general module in the VBE


Sub Button1_click()
Application.Dialogs(xlDialogFormulaFind).Show
End Sub


Get a button from the forms toolbar and put it on the sheet. Right click
on
it and choose assign macro. Assign the above macro.
-------
Click to expand...
Almost works....i have created a button with the VBA code as
instructed and lo and behold it does display the find dialoq box yet
won't return the value searching for even though it is right there in
the spreadsheet...is something missing????


Yvon
 
D

David

Yvon wrote
Almost works....i have created a button with the VBA code as
instructed and lo and behold it does display the find dialoq box yet
won't return the value searching for even though it is right there in
the spreadsheet...is something missing????
Click to expand...

Further reading indicates that the command in a macro requires that a range
be selected first, i.e. Cells.Select to select all the cells on the sheet
(or select a range with the mouse). Results arn't very pretty, though, as
all cells remain selected and only the result isn't shaded.

That said, I believe I would opt for the toolbar method mentioned by Dave
Peterson. It works the same as Ctrl-F without having to select a range
first.
 
E

eluehmann

I have this but it only searches for a text value. If you are just doing a
basic find this may work for you

Sub FindMacro()
findstring = InputBox("What would you like to find?")
Cells.Find(What:=findstring, After:=ActiveCell, LookAt:=xlPart _
, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
 
D

David

eluehmann wrote
I have this but it only searches for a text value. If you are just
doing a basic find this may work for you

Sub FindMacro()
findstring = InputBox("What would you like to find?")
Cells.Find(What:=findstring, After:=ActiveCell, LookAt:=xlPart _
, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
Click to expand...

Good point. I've seen this method in other responses as well.
 
Top