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 :-)  

Posted by andrewe at 09:02

January 10, 2009

Determine Your Excel Version

You may have code that requires a particular Excel version. You can check by using this line of code.

MsgBox Val(Application.Version)

Let's say you have code that requires Excel 2003 or higher, use the following at the beginning of your macro to inform users and exit if a previous version.

If Val(Application.Version) < 11 Then
    MsgBox "You require Excel 2003 or higher to run this code"
    Exit Sub
End If

There is another way you can use. I use the following code to determine how userforms should be shown. vbModeless refers to show forms in such a way that users can still select a range, etc. This was made possible with Excel 2000 and higher versions.

#If VBA6 Then
    frm.Show vbModeless
#Else
    frm.Show
#End If

Back To Basics
I have been thinking about my site a bit and noticed some good and bad things. One thing I think I have strayed away from is simple tips like the one above. And I have added anything to my Formulas,Formats,Shortcuts,VBA Tips for a while too. So for the time being, I will be doing simpler stuff and hopefully more of it :-)  
Posted by andrewe at 08:01