Creating a User Form with variable number of Controls buttons

**Eduard** · 09-04-2008, 08:25 AM

Hi All,

Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?

For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.

Please note the number and name of records in this range may vary.

Thanks,
Eduard

**Leith Ross** · 09-04-2008, 12:43 PM

Hello Eduard,

Yes, you can create controls dynamically on both a VBA UserForm and also on a worksheet. It isn't clear if your are referring to a worksheet as a user form or not. Let me know if your controls are on a worksheet or VBA UserForm.

Sincerely,
Leith Ross

**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