How to Detect Option Button Selection

benjung

New Member
Joined
Apr 28, 2011
Messages
2
Using Excel 2003: I have some form control option buttons in a group on my worksheet. As is, I have subroutines for each, that when you click them it changes the value of a variable (forceSelection). Works fine.

My problem is, when I first open Excel to the worksheet, one of the option buttons is already selected (the one selected when it was last saved). However, the variable forceSelection is not set until an option button is clicked.

So, if the user opens the worksheet and sees that the option they want is already selected, and they click the "RunProgram" button, it fails, since forceSelection doesn't have a value yet in the "RunProgram" subroutine.

In the subroutine for the "RunProgram" button, I tried this code, but it doesn't work:

If Sheets("Weld Data").XForceBtn.Click = True Then forceSelection = 4

The error is:

Run-time error '438':
Object doesn't support this property or method

Thanks ahead of time!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hello Benjung,

Copy this macro into a standard VBA module. Place a call to this macro in your Workbook Open event. Be sure to activate the sheet first. This will reset all the option buttons on the active sheet
Code:
Sub ClearOptionButtons()

  Dim Shp As Shape
  
    For Each Shp In ActiveSheet.Shapes
      If Shp.Type = msoFormControl Then
         If Shp.FormControlType = xlOptionButton Then
            Shp.ControlFormat.Value = False
         End If
      End If
    Next Shp

End Sub
Sincerely,
Leith Ross
 
Upvote 0
I believe you can reset all OptionButtons on the ActiveSheet that are from the Forms Toolbar with this single line of code...

ActiveSheet.OptionButtons.Value = False

So you should be able to replace the code in Leith's macro with this single line of code (and follow the rest of his instructions for using it, of course).
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,763
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top