Create the command Find and Replace on a botton
Results 1 to 5 of 5

Thread: Create the command Find and Replace on a botton
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2007
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create the command Find and Replace on a botton

    Dear reader, I would like to know how to program the code for a command button, in order to when it's clicked, the find and replace application (CTRL+F) pops up.
    Thanks
    -Noubar

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there, welcome to the board!

    Right click any blank space on the toolbar area
    Select Customize
    Click the Commands tab
    Click Edit on the left pane
    Scroll down to Find or Replace, click it and drag it to where you want it

    HTH
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  3. #3
    Board Regular daniels012's Avatar
    Join Date
    Jan 2005
    Location
    34° 56' 19" N / 82° 13' 38" W
    Posts
    5,219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    firefytr,
    This will only work when this worksheet is open... Correct? It will not work in another sheet, yet the button will still exsist...right? if you created a button on the sheet itself, would this not be more efficient?

    I am not being critical, I really just trying to learn myself.

    Michael
    "It is so nice, to see so many, that know so much"

    Using Excel 2007

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Michael, it will actually work - if you dock the button to a toolbar - throughout the entire application. It is actually stored in the XLB file (toolbar). A button on a worksheet is only going to be visible to that worksheet alone. Floating toolbars are application-wide.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  5. #5
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default

    I think it would be just as quick to press CTRL + F but

    Code:
    Sub GetFindControlID()
        Dim a As CommandBarControls
        Dim b As Object
        Dim buf() As Variant
        Dim i As Long
    
        Set a = Application.CommandBars.FindControls
    
        'loop commandbars object
        For Each b In a
            i = i + 1
            ReDim Preserve buf(1 To 2, 1 To i)
            buf(1, i) = b.Caption
            buf(2, i) = b.ID
        Next
    
        'output result
        Application.ScreenUpdating = False
        [A1].Resize(, 2).Value = Array("Caption", "ID")
        [A2].Resize(UBound(buf, 2), 2).Value = myTranspose(buf)
    
        'format a table
        [A1].AutoFormat Format:=xlRangeAutoFormatColor2, Number:=True, Font _
            :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
        [A1].Resize(, 2).HorizontalAlignment = xlCenter
        Application.ScreenUpdating = True
    
        MsgBox "Done"
    End Sub
    
    
    
    Function myTranspose(buf)
        Dim i As Long
        Dim j As Long
        Dim a() As Variant
        ReDim a(1 To UBound(buf, 2), 1 To UBound(buf, 1))
        For i = LBound(buf, 1) To UBound(buf, 1)
            For j = LBound(buf, 2) To UBound(buf, 2)
                a(j, i) = buf(i, j)
            Next
        Next
        myTranspose = a
    End Function
    
    
    
    Sub Test1()
    'this code would show the "Find and Replace" dialogue box.
        Application.CommandBars.FindControl(ID:=1849).Execute
    End Sub
    From http://puremis.net/excel/code/016.shtml

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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