January 25, 2009

FindControl

This is an example of FindControl. You can use it to refer to a command bar control by using it's ID. The following code will add a comment by "executing" the Insert Comment control.

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

Of course, you can just use much simpler to understand code to do the same.

Activecell.Addcomment

There are however other advantages to FindControl, such as using the Font Names control to add items to a combobox on a userform. The code below will use FindControl to get the names, then use the userform events to give a preview of what the font looks like just like the control itself. It also set the font back to the original font to make it more legible when changing to another selection.

Private sFontName As String
Private bChangeFont As Boolean
Private iListIndex As Integer

Private Sub UserForm_Initialize()
    On Error Resume Next

    Dim Fonts
    Dim iAddItem

    iAddItem = 1

    Set Fonts = Application.CommandBars.FindControl(ID:=1728)

    bChangeFont = False

    With ComboBox1

        sFontName = "Tahoma"
        
        .Font.Size = 10

        Do While Err = 0
            .AddItem Fonts.List(iAddItem)

            iAddItem = iAddItem + 1

        Loop
        
        .ListIndex = 0
        .SelStart = 0
        .SelLength = Len(.Text)

    End With
    
    bChangeFont = True

    On Error GoTo 0
End Sub

Private Sub ComboBox1_Change()
    
    If bChangeFont = False Then Exit Sub
    
    With ComboBox1
        .Font.Name = ComboBox1.Value
        iListIndex = .ListIndex
        .SelStart = 0
        .SelLength = Len(.Text)
    End With

End Sub

Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    
    With ComboBox1
        .ListIndex = iListIndex
        .Font.Name = sFontName
        .SelStart = 0
        .SelLength = Len(.Text)
    End With

End Sub

Don't forget you can use the same technique to get the font size too. The ID is 1731 :-)