Assigning a macro to a "button"
 

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???

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???


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" - Find messages by this author
Date: 2000/01/13
Subject: 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.
-------

--
David

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.



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???

--

Dave Peterson

David wrote in message ...
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???


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" - Find messages by this author
Date: 2000/01/13
Subject: 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.
-------
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

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????

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.

--
David

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

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

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

--
David