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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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
 

Forum statistics

Threads
1,140,938
Messages
5,703,278
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top