View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ed Ed is offline
external usenet poster
 
Posts: 399
Default Help declaring array limit with variable?

I'm trying to modify John Walkenbach's code for creating an option-button
UserForm programmatically (http://j-walk.com/ss/excel/tips/tip76.htm). He
shows it as creating a Form with an array of option buttons for each month,
and it works great. I am trying to modify it to create an array of option
buttons for each row of a table, when the number of rows is not known.

John's code is:
Sub DemoGetOption()
Dim Ops(1 To 12) As String
' Create an array of month names
For i = 1 To 12
Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
Next i
UserChoice = GetOption(Ops, 1, "Select a month")
MsgBox UserChoice
End Sub
I tried:
Sub DemoGetOption()
cnt = tbl.Rows.Count
Dim Ops(1 To cnt) As String
but the code errored, saying I need a constant as the second term in the
array declaration.

I also tried:
Sub DemoGetOption()
cnt = tbl.Rows.Count
Dim Ops(1 To 999) As String
For i = 1 To cnt
Ops(i) = doc2.tbl.Cell(i,1).Range.Text
Next i
UserChoice = GetOption(Ops, 1, "Select a month")
MsgBox UserChoice
End Sub

That worked, but since I only have five rows, it gave me lots of empty
option buttons!

How do I either (a) declare my array limits using the "cnt" varable?, or (b)
limit the number of option buttons to only those used?

Ed