How to change the caption of dynamically named Option Buttons?

[lukeh]

Hi guys,

After spending several hours on this and not getting very far I thought I might ask for some help here in case one of you guys could please help. I thought this would be pretty easy to do but as it turns out maybe not (or I'm just doing it totally wrong :/).

What I'm trying to do is... I have a worksheet with 10 data validation drop-down lists (each list uses the same data list which is pulled from a static column on another worksheet). Each drop-down list has exactly 2 ActiveX Option Buttons underneath it. Sort of like this:

DataValidatedList1 (e.g. "Comedy Movie")
OptionButton1List1 (e.g. "Funny") | OptionButton2List1 (e.g. "Not Funny")

DataValidatedList2 (e.g "Horror Movie")
OptionButton1List2 ("Scary") | OptionButton2List2 ("Not Scary")

and so on.

Then whenever a user selects an option from a drop-down list the 2 Option Buttons corresponding to that drop-down list should be updated with custom values as per the example above. Each group of Option Buttons is grouped using VBA property GroupName with the linked cell of the first Option Button set to a cell (so this cell currently shows True if the first Option Button is selected or False if it's not).

Then underneath the Option Buttons I have some information cells that use formulas to display information based on the users choice. Like =IF(ZZ1="True", "Show info for Option Button 1", "Show info for Option Button 2").

Now after a lot of playing around I've been able to solve most of this but there is still one pressing problem I just can't seem to find a solution for.

The easy way to do this would be to simply use Select Case sort of like:

Select Case [the ID of the selected drop-down list]

Case List1
OptionButton1List1.Caption = "Caption 1"
OptionButton2List1.Caption = "Caption 2"

Case List2
OptionButton1List2.Caption = "Caption 1"
OptionButton2List2.Caption = "Caption 2"

etc.

The only issue I have with this approach is that eventually I'll need to copy and paste the lists/option buttons over 100 times and it just seems that it would be very inefficient to have 100+ Select Case statements just to change the captions of 2 Option Buttons.

So what I'm trying to do is find a way to do this dynamically with one lot of code that passes the ID of which list the user selected and then updates the correct Option Buttons automatically.

Currently I have some VBA code in the worksheet_change event that detects when the user selects a different option in the drop-down list and then updates the caption of the 2 Option Buttons based on the selection. Each list cell is a named range.

I know I can use the Target property to find out which list they've selected. However from that I'm then trying to update the correct Option Buttons using code like:

OptionButton1List & TargetListID.Caption = "something"
OptionButton2List & TargetListID.Caption = "something else"

However ActiveX Option Buttons on worksheets don't seem to be able to be dynamically named like this. Is this true? I've also read up on the Controls collection and tried using that but am having no luck... I later found all examples were using VBA forms not a worksheet and I couldn't find any examples for worksheets.

So I'm really confused now... does anyone know how to achieve what I'm trying to do above by being able to dynamically name the Option Buttons with VBA code? Or if my approach won't work is there a better way of doing this?

Thanks in advance to anyone who can help.

 

[lukeh]

If anyone's interested I managed to figure it out.

You can dynamically reference various Option Buttons (or similar) with something like this:

Worksheets("Sheet_Name").OLEObjects("OptionButton" & a & "List" & b).Object.Caption = "Caption 1"

Worksheets("Sheet_Name").OLEObjects("OptionButton" & a+1 & "List" & b).Object.Caption = "Caption 2"

etc.

Cheers.