Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    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>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    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.

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •