Yeah, I need to create a pop up box of sorts that has a dynamic array of
buttons who's captions are loaded from a worksheet column.

any ideas?

Well, you could create just an empty UserForm with the VB Editor and use its
_Activate() event to populate it with buttons. The code below shows how that
can be done and how to obtain the captions from a list on a sheet. What it
doesn't show are things like:
making sure the form is tall enough to handle all the buttons - you could
either size it large enough to hold all possible ones or dynamically size it
during the same event.
assigning actions to take when each of the new buttons is clicked on the form.

For what it's worth to you, here's the basic 'seed' code to get you started.

Private Sub UserForm_Activate()
Dim listRange As Range
Dim rowPointer As Integer
Dim ctrlName As String
Dim nextTopPosition As Integer
'change to name of form with list for captions
'and the starting cell on the sheet with the list
Set listRange = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(listRange.Offset(rowPointer, 0))
'names will be like:
' cmd_00
' cmd_01
' cmd_02 etc.
ctrlName = "cmd_0" & Trim(Str(rowPointer))
Me.Controls.Add _
"Forms.CommandButton.1", ctrlName, True
Me.Controls(ctrlName).Caption = _
listRange.Offset(rowPointer, 0).Value
With Me.Controls(ctrlName)
.Left = 6
.Top = nextTopPosition + 6
.Width = 120
.Height = 24
End With
'adjust next top position
'based on spacing desired and height set
nextTopPosition = nextTopPosition + 6 + 24
rowPointer = rowPointer + 1
Loop
End Sub


"ouch" wrote:

Yeah, I need to create a pop up box of sorts that has a dynamic array of
buttons who's captions are loaded from a worksheet column.

any ideas?

Thanks, I was going to do that but I was hopeingI was missing a command to
create the form programaticaly

"JLatham" wrote:

Well, you could create just an empty UserForm with the VB Editor and use its
_Activate() event to populate it with buttons. The code below shows how that
can be done and how to obtain the captions from a list on a sheet. What it
doesn't show are things like:
making sure the form is tall enough to handle all the buttons - you could
either size it large enough to hold all possible ones or dynamically size it
during the same event.
assigning actions to take when each of the new buttons is clicked on the form.

For what it's worth to you, here's the basic 'seed' code to get you started.

Private Sub UserForm_Activate()
Dim listRange As Range
Dim rowPointer As Integer
Dim ctrlName As String
Dim nextTopPosition As Integer
'change to name of form with list for captions
'and the starting cell on the sheet with the list
Set listRange = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(listRange.Offset(rowPointer, 0))
'names will be like:
' cmd_00
' cmd_01
' cmd_02 etc.
ctrlName = "cmd_0" & Trim(Str(rowPointer))
Me.Controls.Add _
"Forms.CommandButton.1", ctrlName, True
Me.Controls(ctrlName).Caption = _
listRange.Offset(rowPointer, 0).Value
With Me.Controls(ctrlName)
.Left = 6
.Top = nextTopPosition + 6
.Width = 120
.Height = 24
End With
'adjust next top position
'based on spacing desired and height set
nextTopPosition = nextTopPosition + 6 + 24
rowPointer = rowPointer + 1
Loop
End Sub


"ouch" wrote:

Yeah, I need to create a pop up box of sorts that has a dynamic array of
buttons who's captions are loaded from a worksheet column.

any ideas?

Your reply to my post is empty - is there still a question?

"JLatham" wrote:

Well, you could create just an empty UserForm with the VB Editor and use its
_Activate() event to populate it with buttons. The code below shows how that
can be done and how to obtain the captions from a list on a sheet. What it
doesn't show are things like:
making sure the form is tall enough to handle all the buttons - you could
either size it large enough to hold all possible ones or dynamically size it
during the same event.
assigning actions to take when each of the new buttons is clicked on the form.

For what it's worth to you, here's the basic 'seed' code to get you started.

Private Sub UserForm_Activate()
Dim listRange As Range
Dim rowPointer As Integer
Dim ctrlName As String
Dim nextTopPosition As Integer
'change to name of form with list for captions
'and the starting cell on the sheet with the list
Set listRange = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(listRange.Offset(rowPointer, 0))
'names will be like:
' cmd_00
' cmd_01
' cmd_02 etc.
ctrlName = "cmd_0" & Trim(Str(rowPointer))
Me.Controls.Add _
"Forms.CommandButton.1", ctrlName, True
Me.Controls(ctrlName).Caption = _
listRange.Offset(rowPointer, 0).Value
With Me.Controls(ctrlName)
.Left = 6
.Top = nextTopPosition + 6
.Width = 120
.Height = 24
End With
'adjust next top position
'based on spacing desired and height set
nextTopPosition = nextTopPosition + 6 + 24
rowPointer = rowPointer + 1
Loop
End Sub


"ouch" wrote:

Yeah, I need to create a pop up box of sorts that has a dynamic array of
buttons who's captions are loaded from a worksheet column.

any ideas?