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

Good afternoon

There are two ways of doing this : using the Forms toolbar or using the Control Toolbox. I will outline the Forms toolbox method because it is much easier.

Open the forms toolbar, and click on the Button button (if you see what I mean). You can then draw a button on screen and an "Assign Macro" dialog box will open. You choose which macro you want this button to kick off, click Ok and you're done.

To make any changes to your button (macro, font etc) just right click and choose from the list. To change the text on the button, right click then left click on the button image.

HTH.

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" <[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.
-------

--
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 <[email protected]> wrote in message news:<[email protected]>...
> 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" <[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.
> -------
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

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