Macro to hide rows when an option button is selected

MikeGeno

Board Regular
Joined
Apr 4, 2008
Messages
130
I have a spread sheet with 2 option buttons that the user needs choose from.

The first button places a number 1 in cell E66 and the second one places a 2 in E66.

Is there a way to hide rows depending on which botton is pressed.

I would like to hide rows 28-36 if option one is selected and making sure rows 13-25 are visible. and the reverse if option 2 is selected. Hide 13-25 and make sure 28-36 are visible.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Since you only have 2 opposing states, how about 1 button that toggles between the two?

Also, have you already written code to put the value in E66?
 

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325
Enter the following code by Right Clicking the Sheet Tab and Selecting View Code. Copy and Paste the code below.

Private Sub OptionButton1_Click()
Range("E66") = 1
Rows("13:25").EntireRow.Hidden = True
Rows("28:36").EntireRow.Hidden = False

End Sub

Private Sub OptionButton2_Click()
Range("E66") = 2
Rows("13:25").EntireRow.Hidden = False
Rows("28:36").EntireRow.Hidden = True
End Sub
 

MikeGeno

Board Regular
Joined
Apr 4, 2008
Messages
130
Its an official form for our sales reps and I need to have the 2 options showing for them to click option 1 or option 2.

I tried to copy the code into the worksheet but it did not trigger the rows to hide.

I did not code anything to make the value in cell E66 change. In the format control of the 2 buttons under Format is Have checked selected and also reference E66. The button puts its own value there either 1 or 2 depending on which one is clicked
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196

ADVERTISEMENT

In format, have you given the buttons your own names?

If so, replace the OptionButton1 and OptionButton2 portions of the code markoakes posted.

Right click the tab and choose 'view code' - this is where the code should be pasted, if it's not already.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Also, what version are you using and where did your option buttons come from? Are they activeX?
 

MikeGeno

Board Regular
Joined
Apr 4, 2008
Messages
130

ADVERTISEMENT

I am using 2000. I looked at the names and they were auto assigned 16 and 17.

But I found something else. Silly oversight. I did not realize you can assign a macro to an option button. I simply recorded the actions to hide and unhide each set of rows when clicked and assigned it to the buttons

Thank you very much for offering a solution got my mind working again!
 

BELAGE33

New Member
Joined
Nov 29, 2016
Messages
1
Enter the following code by Right Clicking the Sheet Tab and Selecting View Code. Copy and Paste the code below.

Private Sub OptionButton1_Click()
Range("E66") = 1
Rows("13:25").EntireRow.Hidden = True
Rows("28:36").EntireRow.Hidden = False

End Sub

Private Sub OptionButton2_Click()
Range("E66") = 2
Rows("13:25").EntireRow.Hidden = False
Rows("28:36").EntireRow.Hidden = True
End Sub

A detail is missing for this code to work. Please try with an If.
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
Rows("15:18").EntireRow.Hidden = False
End If
End Sub

Private Sub OptionButton2_Click()If OptionButton2.Value = True Then
Rows("15:16").EntireRow.Hidden = False
Rows("17:18").EntireRow.Hidden = True
End If
End Sub

Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
Rows("17:18").EntireRow.Hidden = False
Rows("15:16").EntireRow.Hidden = True
End If
End Sub

Whenever the user changes its choice of button, the rows are hidden or shown.
The buttons must have been grouped so that only one option can be chosen at a time.
 

agall006

New Member
Joined
Feb 7, 2017
Messages
1
Is there a way to use this macro with a dropdown menu that is in a separate worksheet? I have a file with two worksheets, on worksheet one (Assumptions) you select "Incremental" or "Aggregate" in a drop down menu. If you "Incremental" is chosen you want to hide rows 9:27 in the second worksheet (Synergies), if "aggregate" is chosen you want to unhide 9:27 and hide rows 30:51.

Thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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