Creating a User Form with variable number of Controls buttons

**Kuskush** · 09-04-2008, 05:06 PM

You may create a userform with lets say 10 checkboxes and assign the code below to the userform. You have to verify the item indices of the checkboxes (under properties listed as TabIndex). The code as it is assumes index 0 to 9 for the 10 checkboxes.

Private Sub UserForm_Initialize()

Dim i As Long

For i = 1 To 10
If Cells(i, 1) = "" Then
    UserForm1.Controls.Item(i - 1).Visible = False
Else
    UserForm1.Controls.Item(i - 1).Visible = True
    UserForm1.Controls.Item(i - 1).Caption = Cells(i, 1).Value
End If
Next
End Sub

**Eduard** · 09-05-2008, 03:14 AM

Thanks Kuskush! This code might do for a while.

Would there be a more practical solution? Cause I don't want to create a VBA User Form with 100 checkboxes just to make sure that all the records are picked up. Besides that the size of such a User Form will be not very "user-friendly". And the boxes will be dispersed all other the form.

**Eduard** · 09-05-2008, 08:14 AM

Dear Kuskush,

I have one more thing to ask, if you don't mind.

Would it be possible to run a User Form after or within the a macro?

What I'm trying to get, if to take your example, is to sort the cells A1:A10 first and after that the User Form to pop up.

Thanks,
Eduard

**Andy Pope** · 09-05-2008, 09:21 AM

I prefer the listbox approach to this problem where number of entries is variable.

Add a listbox to the userform with this code added to the Initialize event.


ListBox1.ListStyle = fmListStyleOption
ListBox1.MultiSelect = fmMultiSelectMulti
For i = 1 To 10
If Cells(i, 1) <> "" Then
    ListBox1.AddItem Cells(i, 1).Value
End If
Next

**Eduard** · 09-05-2008, 11:00 AM

Thanks Andy. That's a good solution. I was not sure the list box will give me the multiple selection choise. Seems like it's all possible.

Still, I'm trying to figure out where do I have to put the code to sort the data prior to the form being initialized.

Thanks beforehand for any advise.

Eduard

**Andy Pope** · 09-05-2008, 11:04 AM

Either in the initalize event, before the code to deal with checkboxes/listbox.
Or in the Click event of the button

Private Sub CommandButton1_Click()

' code to do thing with cells
' ....

' now show userform
UserForm1.Show

End Sub

**Kuskush** · 09-05-2008, 03:24 PM

Try this:

Private Sub UserForm_Initialize()

Dim i As Long


Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


For i = 1 To 10
If Cells(i, 1) = "" Then
    UserForm1.Controls.Item(i - 1).Visible = False
Else
    UserForm1.Controls.Item(i - 1).Visible = True
    UserForm1.Controls.Item(i - 1).Caption = Cells(i, 1).Value
End If
Next
End Sub