Macro to hide rows when an option button is selected

[MikeGeno]

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.

 

[Weaver]

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]

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]

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]

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]

Also, what version are you using and where did your option buttons come from? Are they activeX?

 

[MikeGeno]

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]

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]

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.