Consulting

Results 1 to 6 of 6

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

  1. #1

    Question 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.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Simple example to get you started
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    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.


    Example Option Buttons.docxExample Option Buttons.docx

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Just read / set them

    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Approach by
    - Collection: for each ... Next
    - by Name: .Oleobjects("optionbutton1")
    - by indexnumber: .oleobjects(1).Name

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •