Setting a VBA form object using a string

Question

Hello,

I am trying to set up a form which is a calendar from which the user can select a date (by default the current month appears). The form consists of 42 command buttons (I have left the default name ie. CommandButton1) which I am setting the day number.

At the moment I have a long-winded section of code for each button (I used Excel to generate this rather than type it all out) which locks and hides the button if it is outside of the month in question which looks like this:

NewDate.CommandButton1.Caption = Format(DATlngFirstMonth - DATintDayNumFirst + DATintX, "dd")
If DATintX < DATintDayNumFirst Then
    With NewDate.CommandButton1
        .Locked = True
        .Visible = DATbooShowExtraDays
        .ForeColor = RGB(150, 150, 150)
    End With
Else
    With NewDate.CommandButton1
        .Locked = False
        .Visible = True
        .ForeColor = RGB(0, 0, 0)
    End With
End If

I know that I can refer to a command button by:

Dim objCommandButton As Object
Set objCommandButton = NewDate.CommandButton1

..which neatens the code up somewhat. But what I would like to do is refer to the command button as a string so I can loop through all 42, ie.

Dim n as integer
n = 1   

Do Until n > 42
Set objCommandButton = NewDate.CommandButton & n
'Some operations
n = n + 1
Loop

Many thanks in advance for assistance.

Answer 1

You can loop through all controls of the form. Try

Sub LoopButtons()
Dim it As Object

For Each it In NewDate.Controls
    Debug.Print it.Name
Next it

End Sub

Then you can put conditional expression (if ... then) in place of Debug.Print or whatever. For example

If Instr(it.Name, "CommandButton") Then
'do your code
end if