Can't get OptionButton choice result from form?!!

[Ed]

I'm *still* trying to modify John Walkenbach's code for creating an
option-button UserForm programmatically
(http://j-walk.com/ss/excel/tips/tip76.htm). Thanks to John Coleman, I can
now set the array to read my table rows and populate with the text of the
first cell in each row.

Now I can't get the result out! I have NO IDEA what I did! Even the MsgBox
didn't work! I modified the end to put the result into a string and MsgBox
the string, but it comes up as a blank MsgBox!

What did I do and how do I fix this?

Ed

Dim Ops As Variant
ReDim Ops(1 To cnt)

Dim i As Integer
Dim UserChoice As Variant

For i = 1 To cnt
strOpBtn = tbl.Cell(i, 1).Range.Text
strOpBtn = Left(strOpBtn, Len(strOpBtn) - 2)
Ops(i) = strOpBtn
Next i
UserChoice = GetOption(Ops, 1, "Select a color")
'If UserChoice <> False Then MsgBox UserChoice
If UserChoice <> False Then strChoice = UserChoice
MsgBox strChoice

[Jake Marx]

Hi Ed,

I don't know what the GetOption function is supposed to return, but it seems
to me that your strChoice assignment is not taking place. That is probably
due to the fact that GetOption is indeed returning False. Try setting a
breakpoint (see Debug menu in the VBE) on that line of code and then run
your macro. When you enter break mode, you can step through line by line,
and you'll hopefully see what's going on. You can hover over variables to
get their values at runtime and/or use the Immediate window to print out
values (use the ? character to output values to the window).

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Ed wrote:
> I'm *still* trying to modify John Walkenbach's code for creating an
> option-button UserForm programmatically
> (http://j-walk.com/ss/excel/tips/tip76.htm). Thanks to John Coleman,
> I can now set the array to read my table rows and populate with the
> text of the first cell in each row.
>
> Now I can't get the result out! I have NO IDEA what I did! Even the
> MsgBox didn't work! I modified the end to put the result into a
> string and MsgBox the string, but it comes up as a blank MsgBox!
>
> What did I do and how do I fix this?
>
> Ed
>
> Dim Ops As Variant
> ReDim Ops(1 To cnt)
>
> Dim i As Integer
> Dim UserChoice As Variant
>
> For i = 1 To cnt
> strOpBtn = tbl.Cell(i, 1).Range.Text
> strOpBtn = Left(strOpBtn, Len(strOpBtn) - 2)
> Ops(i) = strOpBtn
> Next i
> UserChoice = GetOption(Ops, 1, "Select a color")
> 'If UserChoice <> False Then MsgBox UserChoice
> If UserChoice <> False Then strChoice = UserChoice
> MsgBox strChoice