VBA code to access OptionButton properties on a sheet

[KW Slate]

Hey Mr/Mrs Excel guros:

I have placed several OptionButtons on a worksheet, and am trying to write some VBA that will tell me which button is selected (the status or control value). I know on forms you use the “me.” format. Can anyone tell me how I can get VBA to recognize and access these OptionButtons and show me the properties. I am using Form OptionButtons, and have just tried to link them to a cell, but the whole group (4 buttons) is linking to the same cell and shows a value of 1 (True) . My objective is to find out which radio button is checked. Any other ideas?

Thanks so much


KW Slate

 

[Gary McMaster]

Below is some demo code you can try. Please put the code in a standard module in a new workbook. Run the Radio_Demo_Create procedure first.

I believe the bottom line answer to your question is the line:

Set oRadio = oActive.Shapes(Application.Caller)

Hope it helps.

Gary

Public Sub Radio_Select()

Dim oRadio As Shape
Dim oActive As Worksheet

Set oActive = ActiveSheet
Set oRadio = oActive.Shapes(Application.Caller)

MsgBox oRadio.Name 'Shape name (see below)

'If the name of the "Option Button" was changed after insertion it can no longer
'be reference by name as an "Option Button" object. Use the following to retrieve
'the actual "Option Button" object by its index number

Dim oOptButton As OptionButton
Dim iButtonCount As Integer

For iButtonCount = 1 To oActive.OptionButtons.Count
    If oActive.OptionButtons(iButtonCount).Name = Application.Caller Then
        Set oOptButton = oActive.OptionButtons(iButtonCount)
        Exit For
    End If
Next iButtonCount

If Not oOptButton Is Nothing Then
    Debug.Print oOptButton.GroupBox.Name
    Debug.Print oOptButton.BottomRightCell.Address
    Debug.Print oOptButton.TopLeftCell.Address
    Debug.Print oOptButton.Caption
    Debug.Print oOptButton.Border.LineStyle
    Debug.Print oOptButton.Interior.ColorIndex
    Debug.Print oOptButton.Enabled
    Debug.Print oOptButton.OnAction
    'whatever else
End If

End Sub

Public Sub Radio_Demo_Create()

Dim oActive As Worksheet
Dim oRadio As OptionButton
Dim oGroupBox As GroupBox
Dim sGroupCaption As String

Dim oShape As Shape
Dim oButtonRange As Range
Dim oCell As Range

Dim lButtonWidth As Long
Dim lButtonHeight As Long

Dim oStartCell As Range
Dim iNumButtons As Integer

Dim lBoxLeft As Long
Dim lBoxTop As Long
Dim lBoxWidth As Long
Dim lBoxHeight As Long
Dim iBoxOffset As Integer

Application.ScreenUpdating = False

Set oActive = ActiveSheet

'Delete existing option buttons.
For Each oShape In oActive.Shapes
    If InStr(1, oShape.Name, "Option") Then
        oShape.Delete
    End If
Next oShape

'Delete existing group boxes (frames) must be done after deleting buttons.
For Each oShape In oActive.Shapes
    If InStr(1, oShape.Name, "Group") Then
        oShape.Delete
    End If
Next oShape

Set oStartCell = oActive.Range("B2"): iNumButtons = 5: lButtonWidth = 100: lButtonHeight = 12: sGroupCaption = "Option Set 1"
oStartCell.ColumnWidth = 20
GoSub CreateButtons 'Structure as seperate sub or function if desired

Set oStartCell = oActive.Range("B11"): iNumButtons = 3: lButtonWidth = 100: lButtonHeight = 12: sGroupCaption = "Option Set 2"
oStartCell.ColumnWidth = 20
GoSub CreateButtons

Set oStartCell = oActive.Range("D2"): iNumButtons = 7: lButtonWidth = 100: lButtonHeight = 12: sGroupCaption = "Option Set 3"
oStartCell.ColumnWidth = 20
GoSub CreateButtons

Application.ScreenUpdating = True

Exit Sub

CreateButtons:

Set oButtonRange = oActive.Range(oStartCell, oStartCell.Offset(iNumButtons - 1, 0))

iBoxOffset = 4
lBoxLeft = oButtonRange(1).Left
lBoxTop = oButtonRange.Cells(1).Top - iBoxOffset
lBoxWidth = oButtonRange.Cells(1).Width

lBoxHeight = oButtonRange.Cells(oButtonRange.Cells.Count).Top ' + 2 * iBoxOffset
lBoxHeight = lBoxHeight + oButtonRange.Cells(oButtonRange.Cells.Count).Height
lBoxHeight = lBoxHeight - lBoxTop + iBoxOffset

Set oGroupBox = oActive.GroupBoxes.Add(lBoxLeft, lBoxTop, lBoxWidth, lBoxHeight)
oGroupBox.Caption = sGroupCaption
oGroupBox.Visible = True

For Each oCell In oButtonRange

    Set oRadio = oActive.OptionButtons.Add(oCell.Left, oCell.Top, lButtonWidth, lButtonHeight)
    With oRadio
        .Name = "OptionButton" & oCell.Address
        .Caption = .Name
        .OnAction = "Radio_Select"
    End With
    
    If oCell.Address = oButtonRange.Cells(1).Address Then
        oRadio.Value = True
    End If
    
Next oCell

Return

End Sub

 

[KW Slate]

Gary M.

That's a lot of code. It looks good. I'll give it a try and let you know Thanks a bunch.

KW

 

[Gary McMaster]

The bulk of it is only to create a set of buttons for the "intercept" procedure to work with.

You'll probably only need the first 5 lines of the "Radio_Select" procedure.

If you assign the "Radio_Select" macro to each of your existing buttons, I believe it should do what you asked.

Gary