Several Groups of ActiveX Option Buttons - How to Identify Selected Button in Each

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.

Try Right-Clicking a button and selecting "Properties". Then Click the button and R-C it again and select "View Code". Note that you will need to pay attention to Design Mode.

Simple example to get you started

Thanks for the quick response!

Paul, that example confirms that the method I am trying to avoid - using some other method of recording clicks, such as a variable or array - will work to capture changes as the user makes them. However, it doesn't allow me to read the buttons' values via vba code. There should be a DOM naming convention I can use to identify each button, and then use that DOM naming convention to identify each button's properties, and also use that DOM naming convention to change the value of any one button, or group of buttons. I understand how to access the properties window during design time. My issue is that I haven't been able to identify unique option buttons (or groups of option buttons) in vba code.

For example, in the attached the user will click on a graph on sheet "Graphs". In vba code, I want to then jump to the sheet "Filter" which has a series of option buttons in groups, and I want to set the values of those buttons. The option buttons that will have their value set to TRUE will vary depending on which graph was selected on the "Graphs" sheet. I don't see how to set those option buttons via vba code if I can't identify those option buttons via vba code.


Attachment 28013Attachment 28013

Just read / set them

Code:

---

Sub Test()


    MsgBox "OB1 - " & Worksheets("Sheet1").OptionButton1.Value
    MsgBox "OB2 - " & Worksheets("Sheet1").OptionButton2.Value
    MsgBox "OB3 - " & Worksheets("Sheet1").OptionButton3.Value


    MsgBox "Real Name -- " & Worksheets("Sheet2").AnotherRealName.Value


    Worksheets("Sheet1").OptionButton1.Value = Not Worksheets("Sheet1").OptionButton1.Value
    MsgBox "OB1 - " & Worksheets("Sheet1").OptionButton1.Value


End Sub

---

Approach by
- Collection: for each ... Next
- by Name: .Oleobjects("optionbutton1")
- by indexnumber: .oleobjects(1).Name

Code:

---

Sub M_snb()
'  Sheet1
  For Each it In Sheet1.OLEObjects
      MsgBox it.Name & vbLf & it.Object.Value
  Next
 
  For j = 1 To 3
      MsgBox Sheet1.OLEObjects("optionbutton" & j).Object.Caption
  Next

'  Sheet2 
  MsgBox Sheet2.OLEObjects.Count
  For Each it In Sheet2.OLEObjects
    MsgBox it.Name & vbLf & it.Object.Value
  Next
 
  For j = 1 To Sheet2.OLEObjects.Count
      MsgBox Sheet2.OLEObjects(j).Name & vbLf & Sheet2.OLEObjects(j).Object.Caption
  Next
End Sub

---