How to make a toolbar button for find&replace (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Sub Search&ReplaceSpecific()
Application.ScreenUpdating = False
Dim oSheet As Worksheet
Dim oActive As Worksheet
Set oActive = ActiveSheet
For Each oSheet In ThisWorkbook.Sheets
oSheet.Activate
oSheet.Cells.Select
With Selection
.Replace “Giraffe”, “Elephant”, xlWhole, xlByRows, True
End With
Range(“A1”).Select
Next oSheet
oActive.Activate
Application.ScreenUpdating = True
End Sub

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.

Reply | Quote