Macro Help

[JOSTERBAUER]

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?

 

[Damon Ostrander]

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:

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

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