LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
declaring public variable value Damon Excel Programming 4 July 24th 05 02:59 PM
Declaring a Public variable as a Range and its lifetime John Wirt[_5_] Excel Programming 8 January 23rd 05 06:40 AM
Declaring variable as a dynamic array? aiyer[_44_] Excel Programming 1 August 17th 04 11:01 PM
Declaring Variable as VBConstant The Vision Thing Excel Programming 2 May 15th 04 06:35 PM
Declaring a variable? pgoodale[_4_] Excel Programming 2 January 2nd 04 03:26 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"