Results 1 to 2 of 2
2001-07-09, 23:02 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
How to make a toolbar button for find&replace (2000)
I need a toolbar button that will find word "a" and replace it with word "b".
The find and replace in the edit menu works fine, but I need word "a" and word "b" to be default.
Basically I want it set up so that all I do is click the button, and watch it go to work.
I also need it to all sheets in a workbook, not just the visible sheet, if that is even possible.
Please explain your reply "Barney-style" for me, because I am not very familiar with making macros or editting the toolbars.
Spanks. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
2001-07-10, 14:27 #2
- Join Date
- Dec 2000
- Salt Lake City, Utah, USA
- Thanked 6 Times in 6 Posts
Re: How to make a toolbar button for find&replace (2000)
XL97; should work in XL2000. (If necessary unprotect and) Window, Unhide your "personal .xls", which usually opens every time you load Excel. With personal.xls open and active, Press "Alt-F11". You'll see the Visual Basic Editor. Press "Ctrl-R" just to be certain the "Project Exlorer is open (it usually opens by default). Locate the "Personal_Macros (Personal.xls)". There will be a folder for Microsoft Excel Objects. If there's a yellow folder for Modules, skip the next step/sentence. If there's no Modules folder, right-click "Personal_Macros (Personal.xls)" and select Insert, Module. Double click the Modules folder, double-click the first (or any of) the modules "underneath" it. Past this code into it; replace Giraffe and Elephant with the respective Search and Replace words you want.
Application.ScreenUpdating = False
Dim oSheet As Worksheet
Dim oActive As Worksheet
Set oActive = ActiveSheet
For Each oSheet In ThisWorkbook.Sheets
.Replace "Giraffe", "Elephant", xlWhole, xlByRows, True
Application.ScreenUpdating = True
Save the file. Select View, Toolbars, Customize, Commands, in the left pane scroll down and select Macros. Drag the Custom Button (smiley face) to where you want on the toolbar, then click on Modify Selection in the dialog box. In the new Dialog Box, change the Name to What you want, change the Button Image to what you want, click on Assign Macro, in the scroll box find and click on the above macro . OK/Close out, save the file again. The newly assigned toolbar icon should now do what you want.-John ... I float in liquid gardens