Solved

Excel Visual Basic Code for Option Buttons

Posted on 2016-07-15
8
53 Views
Last Modified: 2016-07-15
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.
0
Comment
Question by:f19l
  • 3
  • 3
  • 2
8 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41712847
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
0
 

Author Comment

by:f19l
ID: 41712856
I used your code and I have got the following message:

Run-time error '438':

Object doesn't support this property or method.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41712859
Could you send a dummy?
0
 

Author Comment

by:f19l
ID: 41712883
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41712900
Hi,

pls try

Private Sub OptionButtonYes_Click()

 Sheets("Control").Select
 If Sheets("Sheet1").OptionButtonYes.Value = True Then 'change sheet to your code
     Call GBP_ALPHA_ROLL
 End If

 End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41712914
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.
0
 

Author Comment

by:f19l
ID: 41712924
Actually, using Rgonzo's code works for me. Thanks anyway for your suggestion.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41712928
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. :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now