asked on

Excel Visual Basic Code for Option Buttons

Hello,

I have a very simple excel spreadsheet in which I have two option buttons captioned "Yes" and "No". I have tried to create some VB code so that based upon which button is initially selected, a macro button will perform a specified task when pressed. The code is very basic and listed below.

Private Sub OptionButtonYes_Click()

Sheets("Control").Select
If OptionButtonYes.Value = True Then
Call GBP_ALPHA_ROLL
End If

End Sub

When I try to run it I keep getting the error message:

Run-time error '424':

Object Required

I have tried to fix this myself but have not been able to. Could you please assist?

Thanks.

Rgonzo1971

Hi,

pls try with the name of the control

 If ActiveSheet.Shapes("Option Button Yes").ControlFormat.Value = 0  Then ' 0 IS checked, unchecked -4146

Open in new window

Or you could link a cell for an easier way

Regards

f19l

ASKER

I used your code and I have got the following message:

Run-time error '438':

Object doesn't support this property or method.

Rgonzo1971

Could you send a dummy?

f19l

ASKER

You mean a file? I have attached a very simple file that essentially does what I want.

1) Press the RUN macro button
2) If the "YES" option button is selected a task is performed.
3) If "NO" option button is selected then no task is performed.
TEST.xlsm

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

There is nothing wrong in your original code.

You have an option button from the ActiveX controls not from Form controls, so code suggested by Rgonzo won't work for you.

The following simple code works for me.

Private Sub CommandButton1_Click()
If OptionButtonYes Then
   MsgBox "Yes"
End If
End Sub

Open in new window

If that doesn't work for you that means controls are disabled.

You can test it by this simple code...

Private Sub CommandButton1_Click()
MsgBox "Yes"
End Sub

Open in new window

If that code also doesn't work for you, you will need to fix this issue. To fix this, close all your excel files --> from the control panel, Folder Options, make sure the Show all the hidden files and folder is checked --> now search your system drive for *.exd fiels and delete all the files you get from search result. --> Reboot your system --> Open a blank workbook --> Insert a new command button (activex control) on the sheet and place a simple code for the click event and see if that works for you now.

f19l

ASKER

Actually, using Rgonzo's code works for me. Thanks anyway for your suggestion.

Subodh Tiwari (Neeraj)

I was referring to his first suggested code in the post ID: 41712847. :)
When I was typing, Rgonzo posted his tweaked code so couldn't see it. :)