VBA TIPS
Getting the ID list to execute FindControl method

What if you'd like to show the dialogue box for "Find and Replace" via macro?

Using a SendKeys Statement may help you. But sometimes it fails depending on the situation.

To make sure of that, you can use FindControl method.

Example

The following code would get the ID list to execute FindControl method. (works on Excel 2000 and the later version)

Place the following in an userform module.

Option Explicit

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

Sub Test2()
'this code would show the "Option" dialogue box.
    Application.CommandBars.FindControl(ID:=522).Execute
End Sub


The captions of the controls and the IDs will be output to the worksheet by this code. (shown in the picture below)



Because the ID for showing the dialogue box "Find and Replace" is 1849, the code

Application.CommandBars.FindControl(ID:=1849).Execute

will show the "Find and Replace". (See Sub Test1)




| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo