VBA Variable as CommandButton#

Question

I'm rewriting some code and had a thought, but can't seem to get my syntax right to execute it properly. I want to use a for loop to populate an array of commandbuttons as well as control their visibility. I just need help with my syntax to define which CommandButton number I'm working on in the loop. For instance, CommandButton1, CommandButton2, etc.

Public Sub LoadLots(sName As String, streamLots() As String)
    Label1.Caption = sName
    For o = 1 To 9
        If streamLots(o) <> "" Then
            CommandButton& o &.Caption = streamLots(o)
            CommandButton& o & .Visable = True
        Else
            CommandButton& o & .Visable = False
        End If
    Next
End Sub

Activesheet.Shapes("CommandButton" & o).Caption ... also Visable should be Visible — Scott Holtzman
– Scott Holtzman, Commented Jul 24, 2016 at 21:29

YowE3K · Accepted Answer · 2016-07-24 23:04:24Z

1

Use the Userform.Controls collection to reference the commandbuttons by name.

Public Sub LoadLots(sName As String, streamLots() As String)
    Dim btn As MSForms.CommandButton
    Label1.Caption = sName
    For o = 1 To 9
        Set btn = Me.Controls("CommandButton" & o)
        If streamLots(o) <> "" Then
            btn.Caption = streamLots(o)
            btn.Visible = True
        Else
           btn.Visible = False
        End If
    Next
End Sub

edited Jul 24, 2016 at 23:04

YowE3K

24k7 gold badges28 silver badges45 bronze badges

answered Jul 24, 2016 at 21:44

user6432984

Sign up to request clarification or add additional context in comments.

1 Comment

user6432984 Over a year ago

Glad I was able to help. Thanks for the check.

Collectives™ on Stack Overflow

VBA Variable as CommandButton#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related