Option Buttons -- editing and retrieving data

**hhawken** · 03-10-2022, 09:44 PM

Hello ---
I am rather desperate to receive help with Option Buttons using VBA.

#1 -- Form Control Option Box
I was not using these objects within a form, but rather in a worksheet. Perhaps that should not be done?

I need about 50 sets of Option Boxes. My goal was to create 50 Form Control Group Boxes and place three Option Buttons within each Group Box . Once I linked the Group Box to a cell, it would provide values of 1, 2, or 3 depending which Option Button was selected. Fantastic!!!

Also, I was able to to change the fill color of an Option Button. Using VBA, I could l look at each Option Button and determine which ones were colored.

This seemed to go well, but after creating 7-8 Group Boxes with three Option Boxes inside, the Option Buttons were out of control, acting as if they were not confined by their Group Box. I was getting values greater than 3. This appears to be an obvious error, but I did this VERY CAREFULLY several times with the same frustrating results.

#2 -- ActiveX Controls Option Box
These are very different, but I am able to link the Option Button values to a cell and again determine which Option Button was selected.
However, I need to discover the BackColor of each Option Button.

How can I do this?

Looking forward to your insights.

Thank you!

**AlphaFrog** · 03-10-2022, 11:40 PM

If the borders of your option buttons are not completely within their respective group box boarders, then they are not part of that group and the weirdness happens. An option button boarder cannot be at all outside its' groupbox.

Here is example code that adds three option buttons within a groupbox to 50 rows in column B of a blank worksheet.

Sub Forms_OptinButtons_Demo_Code()
    Application.ScreenUpdating = False
    Sheets.Add after:=Sheets(Sheets.Count) 'blank sheet for demo
    Rows("1:50").RowHeight = 20
    Columns("B").ColumnWidth = 20
    For i = 1 To 50
        With Cells(i, "B")
            ActiveSheet.GroupBoxes.Add(.Left, .Top, .Width, .Height).Caption = ""
            'Three Option Buttons per group
            For j = 1 To 3
                With ActiveSheet.OptionButtons.Add(.Left + (j - 1) * 20, .Top, 20, .Height)
                    .Caption = j
                    .LinkedCell = "A" & i
                    '.Interior.Color.Index = 44
                    '.OnAction = "MyMacro"
                End With
            Next j
        End With
        
    Next i
    Application.ScreenUpdating = True
End Sub

Option Buttons -- editing and retrieving data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Option Buttons -- editing and retrieving data

Re: Option Buttons -- editing and retrieving data

Thread Information

Users Browsing this Thread

Similar Threads

enable certain combo buttons based on user selected option buttons

[SOLVED] Multiple option buttons - Need VBA to disable second group of option buttns based on respo

How can I insert multiple sets of option buttons (radio buttons) in Excel 2010?

[SOLVED] Disabling an Entire Frame with Option Buttons if other Option Buttons are not clicked.

[SOLVED] option buttons for data table

Using option buttons to import data from Notepad

Having option buttons fill in data conditionally

Gathering Data from Option Buttons

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions