Creating checkboxes in userform depending on parameters

Question

I want an user to select on which sheets he wants to create a new line of text. But the number of sheets he is able to select may vary over time and I don't want to hardcode the sheets' name.

Here is an example ("o" represents the checkbox) of what I aim to do:

o 01.2013
o 07.2013
o 01.2014
o 07.2014

I created an userform with an empty frame to put my checkboxes, and added this bit of code to the userform:

Private Tck(10) As MSForms.CheckBox

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim i As Long

i = 1

For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, 3) = "T2M" Then
      Set Tck(i) = Frame1.Controls.Add("Forms.Checkbox.1", "Checkbox" & i)
      Tck(i).Caption = Right(ws.Name, 7)
      i=i+1
    End If
Next

End Sub

But it only adds one checkbox with the last sheet which validates the if test.

I tried to make an offset between the two iterations but I can't modify the position of the Tck(i) using Tck(i).top for example.

I also tried the method from the answer of this question : Adding controls to a frame in an Excel userform with VBA but it dosen't work either.

RubberDuck · Accepted Answer · 2014-09-02 12:16:52Z

2

Your checkboxes are there, you just can't see them because they're overlayed on top of each other. You had the right idea with changing the 'Top' value.

Public Sub addCheckboxes()
    Dim ws As Worksheet
    Dim i As Integer
    Dim tck As MSForms.CheckBox
    Dim offset As Integer: offset = 5

    For i = 1 To Worksheets.Count
        Set ws = Worksheets(i)
        Set tck = Frame1.Controls.Add("Forms.Checkbox.1", "Checkbox" & i, True)
        tck.Top = offset

        offset = offset + 15

    Next i
End Sub

answered Sep 2, 2014 at 12:16

RubberDuck

12.9k5 gold badges56 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

Creating checkboxes in userform depending on parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related