Creating a User Form with variable number of Controls buttons

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