TheArtMan
02-24-2021, 03:46 PM
I have an Excel spreadsheet with option buttons in multiple groups. I am attempting to identify which option button is selected in each group so that I can perform other actions based on those selections. How do I identify which button in each group has been selected?
They are all named ActiveX Option Buttons on the sheet (not Form Controls and not on a custom UserForm). Each Group is uniquely named. For example, I have 3 buttons named obYesNETLogins, obNoNETLogins and obIgnoreNETLogins. Those 3 buttons are in the group named NETLogins. I have obYesTabletLogins, obNoTabletLogins and obIgnoreTabletLogins in the group TabletLogins. All buttons and groups follow the same naming logic. I did not leave any of them at the default "Button1", "Button2", etc.
When I couldn't seem to get the values of those buttons, I tried a simple sub to at least identify a button using the following:
For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object) = "OptionButton" Then
If obj.Object.Value = True Then
MsgBox obj.Object.Caption & " was selected!"
Exit Sub
End If
End If
Next obj
I received nothing when running this code. I expected to get something.
The block of code is not my problem - that just illustrates how I was attempting to identify the buttons. Everything I have read indicates that those ActiveX Option Buttons are OLEObjects of TypeName "OptionButton", but clearly I'm missing something. I could create a table spreadsheet that gets cells updated with the click event of the buttons, but that will update and cause recalculation and screen refresh every time a button is selected. I should be able to read the values of the buttons once they are all selected (and the user clicks a command button to Apply Filters) and take actions only once.
I would appreciate any help in determining the values of those buttons per group using VBA code instead of a klugy spreadsheet table.
They are all named ActiveX Option Buttons on the sheet (not Form Controls and not on a custom UserForm). Each Group is uniquely named. For example, I have 3 buttons named obYesNETLogins, obNoNETLogins and obIgnoreNETLogins. Those 3 buttons are in the group named NETLogins. I have obYesTabletLogins, obNoTabletLogins and obIgnoreTabletLogins in the group TabletLogins. All buttons and groups follow the same naming logic. I did not leave any of them at the default "Button1", "Button2", etc.
When I couldn't seem to get the values of those buttons, I tried a simple sub to at least identify a button using the following:
For Each obj In ActiveSheet.OLEObjects
If TypeName(obj.Object) = "OptionButton" Then
If obj.Object.Value = True Then
MsgBox obj.Object.Caption & " was selected!"
Exit Sub
End If
End If
Next obj
I received nothing when running this code. I expected to get something.
The block of code is not my problem - that just illustrates how I was attempting to identify the buttons. Everything I have read indicates that those ActiveX Option Buttons are OLEObjects of TypeName "OptionButton", but clearly I'm missing something. I could create a table spreadsheet that gets cells updated with the click event of the buttons, but that will update and cause recalculation and screen refresh every time a button is selected. I should be able to read the values of the buttons once they are all selected (and the user clicks a command button to Apply Filters) and take actions only once.
I would appreciate any help in determining the values of those buttons per group using VBA code instead of a klugy spreadsheet table.