Macro Help

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
I am looking for a macro that will either run off of an if formula, or an option button.

1) if trimmer is in a2 it will run a macro
2) option box trimmer is selected it will run a macro.

I was wondering if there is a way to split up the option boxes. ex you have a group of options option 1, 2, and 3. Then options 4, 5, and 6. If you select option 1 it will not clear out any of the 4, 5, and 6 options, but will clear out the 2 & 3 option if option 1 is selected. Is this possible?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi JOSTERBAUER,

I am assuming you mean optionbuttons rather than checkboxes (you mentioned "option boxes").

You can control the groupings of ActiveX optionbuttons (the ones from the Controls Toolbox toolbar) using the GroupName optionbutton property. Simply select optionbuttons 1, 2, and 3 (all at once) and set their GroupName to a unique name (like "Set1"), and do the same for 4, 5, and 6 ("Set2"). Optionbuttons with the same group name will be the ones that are linked together.

Regarding 1) and 2), I assume that in 1) "trimmer" is a text string entered in cell A2, whereas in 2) it is the name of the optionbutton. Either functionality can be easily achieved. To get 1), simply create a worksheet change event like this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$2" Then
      Application.EnableEvents = False
      MyMacro
      Application.EnableEvents = True
   End If
End Sub

where MyMacro is the name of your macro. This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste this code into the Code pane.

To accomplish 2), place this code in the worksheet event code module (or in the userform's event code module if the optionbuttons reside on a userform):

Code:
Private Sub trimmer_Click()
   MyMacro
End Sub

where again I am assuming that the optionbutton's name is "trimmer". This code will run MyMacro when optionbutton trimmer is selected.

Keep Excelling.

Damon
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,721
Members
410,630
Latest member
JFORTH97
Top