Option Buttons: Selection in One Group Enables the Next Group

Hello,

Thanks in advance for any help/advice. I am in need of simple VBA code for the following user experience.

I have 3 groups of option buttons (GroupName for each are: "Group1" with 4 buttons, "Group2" with 3 buttons, and "Group3" with 3 buttons). When a user opens the workbook I have code to set all Values for all buttons to be False. I also have Group2 and Group3 option button Enabled values set to False. I would like when a choice is made in Group1, Group2 option buttons become enabled and allow the user to make a selection for Group2. Then, when a selection in Group2 is made, Group3 option buttons become enabled and allow the user to make a selection in Group3. I'm looking for this conditional user experience to force selections in the desired manner for the business case.

These are ActiveX buttons on a worksheet, not in a userform. I think I can come up with a "long winded" solution by option button click events, but have to believe there's something a bit more elegant that can use the GroupName field so when there's a selection made for any button in Group1, Group2 becomes enabled and so on.

Any help is much appreciated, so thank you!

Regards,
Chris

Hi Chris,

See the attached Example file (created and tested using Excel 2003) which attempts to implement your requirements. All ActiveX OptionButton clicks are processed by a Class Module. While advanced in theory, the implementation should be relatively easy. Two modules from the attached file should be imported into your application.

I suggest you try the Example file first. If the Example file doesn't work at first, select the 'Delete Controls' Shape, and then the 'Create Controls' shape. I have had problems in the past with using ActiveX controls in different Excel versions.

Installation Instructions (using the Example file as a baseline):
a. Import module 'ClassOptionButtonEvent' to your application
b. Import module 'ModOptionButtons' into your application
c. The only items in 'ModOptionButtons' that are required are:
(1) Declarations at the top of the module
(2) Sub EnableOptionButtonEvents()
(3) Sub ProcessActiveXOptionButtonClick()

Thanks to Andy Pope and Chip Pearson for the original code this was adapted from.

Lewis

Class Module 'ClassOptionButtonEvent' code:

Please Login or Register  to view this content.

Ordinary Code module 'ModOptionButtons':

Please Login or Register  to view this content.

Thanks for this Lewis! I'll go in and take a look at the example and then see if I can make it happen for my project. Appreciate sharing the source too. I swear I search up and down for things but sometimes it's the proverbial needle in a haystack trying to get something close. Plus being at work doesn't help getting pulled in 15 directions. Anyways, I'll be back with feedback after I give this a shot.

In gratitude,
Chris