Macros and Option Buttons

[rob1987]

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

 

[MickG]

Try this:-
The code sets each "OptionButton " to true and will run the Code in each OptionButton_Click Event.

Dim Shp As OLEObject
For Each Shp In ActiveSheet.OLEObjects
    If TypeName(Shp.Object) = "OptionButton" Then
        Shp.Object = True
    End If
Next Shp

 

[rob1987]

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]

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]

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]

You need to be running it from a sheet control, like a CommndButton on the sheet, like this:-

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:-

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.

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

 

[Norie]

This is sort of what I meant http://www.box.net/shared/4jzqad3j0k.

 

[rob1987]

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