Master command button to expand / collapse rows

[jonnynacker]

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

 

[mikerickson]

There is no need to select.

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?

 

[Rick Rothstein]

There is no need to select.

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...

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

 

[jonnynacker]

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]

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]

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