CODEWITHSUNDEEP
excelvbacheckbox
Tom
Tom

Reputation: 169

Creating checkboxes in userform depending on parameters

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.

Upvotes: 1

Views: 734

Answers (1)

RubberDuck
RubberDuck

Reputation: 12788

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

Upvotes: 2

Related Questions