Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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??? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Button | Excel Discussion (Misc queries) | |||
How to assign a macro to a commnd button | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
assign a macro to a control button | Excel Discussion (Misc queries) | |||
Macro button colour change??? | Excel Worksheet Functions |