Create the command Find and Replace on a botton

noubarp

New Member
Joined
Sep 11, 2007
Messages
1
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top