Macros and Option Buttons

rob1987

New Member
Joined
Feb 14, 2011
Messages
39
Hello,

I have a spreadsheet with 50 rows of data in - at the end of each row is an option button which, when pressed, runs a macro depending on set criteria. At the moment I have to go through all option buttons manually to press them. I would like to insert an overall command button that when pressed will in effect select each option button and run each macro. Can somebody please advise how to do this?

I have tried linking them all through one cell (and I have tried to do this with checkboxes too). When I tried with checkboxes I can either check or uncheck them all via the one box - but I can't get the macro to run when they are checked.

Any advise on the best way to do this would be greatly appreciated. Many Thanks
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
The code sets each "OptionButton " to true and will run the Code in each OptionButton_Click Event.
Code:
Dim Shp As OLEObject
For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "OptionButton" Then
        Shp.Object = True
    End If
Next Shp
 

rob1987

New Member
Joined
Feb 14, 2011
Messages
39
Hi Mike, thanks for the reply.

I tried your code but it says "invalid outside procedure". Do I need to edit any of it to reflect my worksheet names or anything?

Thanks for your help
 

rob1987

New Member
Joined
Feb 14, 2011
Messages
39
Sorry it didn't want assigning to the sheet, I see, my fault.

However it now says Run time error 438. Does it need to be an ActiveX Option Button or something?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What does the code for each button do?

Is it basically the same for each one with only things like the row/range involved changing?

If that's what's happening you could set up a separate sub that can be called from each button.

You would create the sub so that it takes a parameter(s)/argument(s) that indicate which row/range to work on.

So you could call that sub from each option button's click event.

For the 'all' button you could set it up so the called sub replicates whatever would happen if all the buttons were pressed.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
You need to be running it from a sheet control, like a CommndButton on the sheet, like this:-
Code:
Private Sub CommandButton1_Click()
Dim Shp As OLEObject
For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "OptionButton" Then
        Shp.Object = True
    End If
Next Shp
End Sub
or perhaps a basic module , like this:-
Code:
sub Mycode()
Dim Shp As OLEObject
For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "OptionButton" Then
        Shp.Object = True
    End If
Next Shp
End Sub
If you not familier with those try running from a Worksheet event as follow:-
Right click sheet tab:- Select "ViewCode", VB window appears .
Paste code below into VB Window.
Close VB Window
Double Click in cell "A1".
Code should now run to Option Buttons.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Shp As OLEObject
If Target.Address(0, 0) = "A1" Then
For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "OptionButton" Then
        Shp.Object = True
    End If
Next Shp
End If
End Sub
Mick
 

rob1987

New Member
Joined
Feb 14, 2011
Messages
39
Hi Norie,

Each option button has very similar code but it just refers to a different range in each one.

I'll have a look at what you've put and also Micks to see which is best.

Many Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,109,385
Messages
5,528,396
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top