Master command button to expand / collapse rows

jonnynacker

New Member
Joined
Sep 11, 2014
Messages
46
I currently have a spread sheet with several command buttons on it which expands / collapses various rows. the code I use for each button isPrivate Sub CommandButton5_Click()
Rows("12:13").Select
Selection.EntireRow.Hidden = IIf(Selection.EntireRow.Hidden, False, True)
End Sub. I am looking to insert a command button at the top which will expand / collapse all of these sub command buttons regardless of which state they are in. Please help
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
There is no need to select.
Code:
Sub Button1_Click()
    With Sheet1.Rows("12:13").EntireRow
        .Hidden = Not (.Hidden)
    End With
End Sub

What are all the ranges in that need to be gang un/hidden?
If some of these rows are hidden and some are visible, what do you want to happen when the user presses the master button:
1) hidden rows become visible, visible become hidden. Repeated click will toggle each click.
2) all rows become hidden, next click all visible, etc.

Are these buttons on a userform or a worksheet?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
There is no need to select.
Code:
Sub Button1_Click()
    With Sheet1.Rows("12:13").EntireRow
        .Hidden = Not (.Hidden)
    End With
End Sub
You should not need to specify EntireRow when using Rows. Also, if the button is an ActiveX control (which I think it is given the CommandButton1 name), then the Sub is in the Sheet module which means the Sheet1 reference is not needed as well...
Code:
Private Sub CommandButton5_Click()
  Rows("12:13").Hidden = Not Rows("12:13").Hidden
End Sub



Are these buttons on a userform or a worksheet?
The OP started by saying "I currently have a spread sheet with several command buttons on it...", so I assumed they were directly on the sheet (which influenced my assumption they are ActiveX controls given their names).



The OP can unhide all the rows at once by executing this code line...

Sheet2.Rows.Hidden = False

and he can hide multiple rows all at once by putting his range addresses, comma delimited, inside quote marks structured like this...

Sheet2.Range("12:14,17:20,31:45").EntireRow.Hidden = True
 
Last edited:

jonnynacker

New Member
Joined
Sep 11, 2014
Messages
46
so there are 83 command buttons which all expand / collapse roughly 3 rows under them on a worksheet. So my aim is to make a command button at the top on the worksheet that once pressed will collapse all or expand all regardless of the amount of rows hidden and un-hidden. so once this button is pressed it will give the user a fully expanded worksheet or fully collapsed worksheet
 

jonnynacker

New Member
Joined
Sep 11, 2014
Messages
46
Sorry if my reply's aren't formatted properly, I'm new to this website.
So I have got to this stage, from sheet 1 a cell will be given a value of Category 1, Category 2 or Category 3.
From that you press this command button which takes you to the next sheet and depending on the category will only show you the rows that are applicable, I keep getting an error with what I am currently using.

Sub Button15_Click()
If (Sheet1.Cells(6, 3) <> "") And (Sheet1.Cells(10, 3) <> "") And (Sheet1.Cells(11, 3) <> "") And (Sheet1.Cells(12, 3) <> "") And (Sheet1.Cells(13, 3) <> "") And (Sheet1.Cells(14, 3) <> "") Then
If (Sheet1.Cells(18, 3) = "CATEGORY 1") Then
MsgBox "Your Project has been classed as Category 1 and will now follow the appropriate process.", vbOKOnly, "Attention"
Sheet4.Rows("11:16,21:24,28:42").EntireRow.Hidden = True
Sheet4.Activate
ElseIf (Sheet1.Cells(18, 3) = "CATEGORY 2") Then
MsgBox "Your Project has been classed as Category 2 and will now follow the appropriate process.", vbOKOnly, "Attention"
Sheet4.Activate
ElseIf (Sheet1.Cells(18, 3) = "CATEGORY 3") Then
MsgBox "Your Project has been classed as Category 3 and will now follow the appropriate process.", vbOKOnly, "Attention"
Sheet4.Activate
ElseIf (Sheet1.Cells(18, 3) = "USE PORTAL TO COMPLETE") Then
MsgBox "too large for AMPS, Please use Portal.", vbOKOnly, "Error"
Else
MsgBox "Please populate all fields to progress.", vbOKOnly, "Error"
End If
Else
MsgBox "Please populate all fields to progress.", vbOKOnly, "Error"
End If
End Sub

It gets an error in the line trying to hide the rows
 

jonnynacker

New Member
Joined
Sep 11, 2014
Messages
46
Sorry if my reply's aren't formatted properly, I'm new to this website.
So I have got to this stage, from sheet 1 a cell will be given a value of Category 1, Category 2 or Category 3.
From that you press this command button which takes you to the next sheet and depending on the category will only show you the rows that are applicable, I keep getting an error with what I am currently using.

Code:
Sub Button15_Click()
 If (Sheet1.Cells(6, 3) <> "") And (Sheet1.Cells(10, 3) <> "") And (Sheet1.Cells(11, 3) <> "") And (Sheet1.Cells(12, 3) <> "") And (Sheet1.Cells(13, 3) <> "") And (Sheet1.Cells(14, 3) <> "") Then
    If (Sheet1.Cells(18, 3) = "CATEGORY 1") Then
        MsgBox "Your Project has been classed as Category 1 and will now follow the appropriate process.", vbOKOnly, "Attention"
        Sheet4.Rows("11:16,21:24,28:42").EntireRow.Hidden = True
        Sheet4.Activate
    ElseIf (Sheet1.Cells(18, 3) = "CATEGORY 2") Then
        MsgBox "Your Project has been classed as Category 2 and will now follow the appropriate process.", vbOKOnly, "Attention"
        Sheet4.Activate
    ElseIf (Sheet1.Cells(18, 3) = "CATEGORY 3") Then
        MsgBox "Your Project has been classed as Category 3 and will now follow the appropriate process.", vbOKOnly, "Attention"
        Sheet4.Activate
    ElseIf (Sheet1.Cells(18, 3) = "USE PORTAL TO COMPLETE") Then
        MsgBox "too large for AMPS, Please use Portal.", vbOKOnly, "Error"
    Else
        MsgBox "Please populate all fields to progress.", vbOKOnly, "Error"
    End If
 Else
    MsgBox "Please populate all fields to progress.", vbOKOnly, "Error"
 End If
 End Sub

It gets an error in the line trying to hide the rows
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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