I have a Userform that is initiated by user selection of certain cells, and I
need to display a number of CommandButtons corresponding to the length of a
list associated with the user selection. My form has the maximum number of
buttons I ever expect, and a macro sizes it to display only those needed.
I’m looking for a simple loop to add captions from the list, but I don’t see
any way to index the button reference in a loop. What I’m trying to do:

For n = 1 to List Count
CommandButtonn.Caption = Range(“List”).Offset( n, 0)
Next

Obviously that won’t work, so I seem to be headed for a brute force method.
Is there a more elegant way?

--
Al C

Hi

Assuming the CommandButtons is named "CommandButton1", "CommandButton2"....

Try this code

For n = 1 To ListCount
For Each c In Me.Controls
If c.Name = "CommandButton" & n Then
c.Caption = Range("List").Offset(n, 0).Value
Exit For
End If
Next
Next

Regards,
Per

"Al" <(E-Mail Removed)> skrev i meddelelsen
news:1BA787B1-3D15-49CE-9D13-(E-Mail Removed)...
>I have a Userform that is initiated by user selection of certain cells, and
>I
> need to display a number of CommandButtons corresponding to the length of
> a
> list associated with the user selection. My form has the maximum number
> of
> buttons I ever expect, and a macro sizes it to display only those needed.
> I’m looking for a simple loop to add captions from the list, but I don’t
> see
> any way to index the button reference in a loop. What I’m trying to do:
>
> For n = 1 to List Count
> CommandButtonn.Caption = Range(“List”).Offset( n, 0)
> Next
>
> Obviously that won’t work, so I seem to be headed for a brute force
> method.
> Is there a more elegant way?
>
> --
> Al C

Try it this way...

Private Sub UserForm_Initialize()
Dim N As Long
For N = 1 To Range("List").Count
Me.Controls("CommandButton" & N).Caption = _
Range("List").Cells(N, 1).Value
Next
End Sub

Rick


"Al" <(E-Mail Removed)> wrote in message
news:1BA787B1-3D15-49CE-9D13-(E-Mail Removed)...
>I have a Userform that is initiated by user selection of certain cells, and
>I
> need to display a number of CommandButtons corresponding to the length of
> a
> list associated with the user selection. My form has the maximum number
> of
> buttons I ever expect, and a macro sizes it to display only those needed.
> I’m looking for a simple loop to add captions from the list, but I don’t
> see
> any way to index the button reference in a loop. What I’m trying to do:
>
> For n = 1 to List Count
> CommandButtonn.Caption = Range(“List”).Offset( n, 0)
> Next
>
> Obviously that won’t work, so I seem to be headed for a brute force
> method.
> Is there a more elegant way?
>
> --
> Al C

Thank you both. That got me past a mental lblock.
--
Al C