Cycling through a group of Option Buttons

[j_Southern]

I have a userform which shows a group of OptionButtons all in the group called "Accs". All I want to do is have the user choose one and for the caption of the chosen button to be placed as a value in the range A2. I have found a way to do this but it is very ponderous and ugly. I am hoping to be shown something much more elegant!
The attachment shows the userform with the Option Buttons
Thanks in advance
John

[6StringJazzer]

You don't need to cycle, just use the Click event for each button. The first Sub is called by each click event to store the caption in A2. This makes it trivial to change if you decide to save the caption in a different cell. This will save the value in the currently active sheet, so qualify the sheet explicitly if that's not what you want.

Hopefully you do not find this ponderous and ugly There is another way to do this that is more robust and elegant but it requires slightly more complex code including a Class and IMHO doesn't really buy you anything in this case.

Please Login or Register  to view this content.

[j_Southern]

Jeff
Many thanks for your suggested code, it is a lot neater than my attempt. I was thinking along the lines ( including your SaveCaption)-

Please Login or Register  to view this content.

Can this be turned into workable code? If it can I think I would feel that it was about right.
John

[6StringJazzer]

I thought about this some more and I realized that you have presented a question in isolation without explaining what you are doing. So I answered your question exactly as asked without actually solving your problem

The answer I gave you is good if you want to store that value every time you click a button. But that is not usually how people design these things. I suspect that what you may really want is to have a Submit or Close button, and store the value when the user hits that button. In that case your code concept is OK and would look like this:

Please Login or Register  to view this content.

You must do the loop on a generic Control object because there is no collection for each specific type of control. Then when you find an OptionButton, you set it to a specific OptionButton object so you can access the GroupName property (not available in a Control object).

Throw out all that other stuff I did, except for SaveCaption.

[j_Southern]

Jeff, thank you very much for your code and explanation. I found them very helpful.
It does seem odd to me that such a useful idea as Group is relegated so far down the hierarchy of objects and properties.
However I am now up and running.
John

[6StringJazzer]

Yes, in my opinion this is a flaw in the object model. There is no collection called OptionButtons where you can loop through all the option buttons, so you have to loop through Controls. GroupName is unique to OptionButtons, so a Control doesn't have it. Once you determine that a Control is an OptionButton, you have to explicitly convert it to to access the GroupName property.

I suppose you could also use Object instead of Control and this might work without conversion but I haven't tried it.