How to dynamically add Checkbox-Buttons to userform in VBA

Question

I have a userform with 5 checkbox buttons for 5 pdf versions. Well, when the user calls the userform, then the userform initializes 5 checkbox buttons to select one of them. At the moment, the code is very static and not so good.

Here the example:

If rs.EOF = False Then
  Do Until rs.EOF Or i = 5
     Select Case i
       Case Is = 0
         frmOne.Version5.Visible = True
         frmOne.Version5.Caption = rs!versNo & "#" & rs!versFrom
         frmOne.Version5.tag = rs!versNo & "_" & rs!FiD & ".pdf"
       Case Is = 1
         frmOne.Version4.Visible = True
         frmOne.Version4.Caption = rs!versNo & "#" & rs!versFrom
         frmOne.Version4.tag = rs!versNo & "_" & rs!FiD & ".pdf"
       Case Is = 2
         frmOne.Version3.Visible = True
         frmOne.Version3.Caption = rs!versNo & "#" & rs!versFrom
         frmOne.Version3.tag = rs!versNo & "_" & rs!FiD & ".pdf"
       Case Is = 3
         frmOne.Version2.Visible = True
         frmOne.Version2.Caption = rs!versNo & "#" & rs!versFrom
         frmOne.Version2.tag = rs!versNo & "_" & rs!FiD & ".pdf"
       Case Is = 4
         frmOne.Version1.Visible = True
         frmOne.Version1.Caption = rs!versNo & "#" & rs!versFrom
         frmOne.Version1.tag = rs!versNo & "_" & rs!FiD & ".pdf"
    End Select
    i = i + 1
    rs.MoveNext
  Loop
End If

To much code I think. So my intention was to define it like the example below, but this doesn't work:

If rs.EOF = False Then
    For i = 1 To 5
      With frmOne
         .Version & i &.Visible = True
         .Version & i &.Caption = rs!versNo & "#" & rs!versFrom
         .Version & i &.tag = rs!versNo & "_" & rs!FiD & ".pdf"
      End With
      rs.MoveNext
    Next i
End If

Do have anyone an idea how could I fix that?

Rory · Accepted Answer · 2016-05-23 13:44:57Z

1

You can refer to the Controls collection using the name:

If rs.EOF = False Then
    For i = 1 To 5
      With frmOne.Controls("Version" & i)
         .Visible = True
         .Caption = rs!versNo & "#" & rs!versFrom
         .tag = rs!versNo & "_" & rs!FiD & ".pdf"
      End With
      rs.MoveNext
    Next i
End If

To actually add the controls at runtime too:

Do While not rs.EOF
    i = i + 1
      With frmOne.Controls.Add("Forms.CheckBox.1", "Version" & i, True)
         .Caption = rs!versNo & "#" & rs!versFrom
         .tag = rs!versNo & "_" & rs!FiD & ".pdf"
      End With
      rs.MoveNext
Loop

edited May 23, 2016 at 13:44

answered May 23, 2016 at 13:13

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

7 Comments

yuro Over a year ago

And what is, when the checkboxes not predefined in the userform? How can I add the checkboxes to the userform?

Rory Over a year ago

Use the Add method of the controls collection.

yuro Over a year ago

You mean just to define With frmOne.Controls.Add("Version" & i)...

yuro Over a year ago

When I do this, I'm getting the runtime error: '-2147221005 (800401f3)' invalid class string

Rory Over a year ago

Use the Left, Top, Height and Width properties.

|

user3598756 · Accepted Answer · 2016-05-23 13:13:25Z

1

go like follows:

If rs.EOF = False Then
    For i = 1 To 5
        With frmOne.Controls("Version" & i) '<~~ use Controls collection of Userform object
           .Visible = True
           .Caption = rs!versNo & "#" & rs!versFrom
           .Tag = rs!versNo & "_" & rs!FiD & ".pdf"
        End With
        rs.MoveNext
    Next i
End If

answered May 23, 2016 at 13:13

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

1 Comment

yuro Over a year ago

look to the comment below!

Collectives™ on Stack Overflow

How to dynamically add Checkbox-Buttons to userform in VBA

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related