Combine 2 Macros

[jpeters7635]

I have 2 macros - (Show_Hide) which hides and unhides the section by clicking a button, and another macro (BudgtComm) that only shows 2 non-contiguous rows within the section and only hides.

I'd like to combine these into one macro so that when I hit the button with all rows showing, the budgtcomm macro becomes the view, then click again and it hides the entire section, and then click again and it unhides to the budgtcomm view, and then click again and it unhides the whole section. I appreciate any help!

This is the one that shows and hides the whole section with one button:
_______________________________________________________________

Sub show_hide()
Rows("139:146").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True
End If
End Sub
__________________________________________________________________

This one only shows only rows 139, 143, and 145 - 146
__________________________________________________________________

Sub BudgtComm()
Range("140:142,144:144").EntireRow.Hidden = True
End Sub
___________________________________________________________________

Is there a way to step the budgtcomm section so that the budgtcomm shows between the show/hide?

 

[johnnyL]

I'm not really sure what you are trying to do, but if I understand correctly, I think using what you have provided, would look something like:

Sub show_hide()
'
    Rows("139:146").Select
'
    If Selection.EntireRow.Hidden = False Then              ' If all rows are showing then ...
        Range("140:142,144:144").EntireRow.Hidden = True    '   Show just the budgtcomm rows, 139, 143, and 145 - 146
        budgtcommFlag = "True"
'
    ElseIf budgtcommFlag = "True" Then                      ' If we have gone from all rows showing to just showing budgtcomm then ..
        Selection.EntireRow.Hidden = True                   '   Hide all the rows
        budgtcommFlag = "False"
'
    ElseIf Selection.EntireRow.Hidden = True Then           ' If all rows are hidden then ...
        Range("139,143,145:146").EntireRow.Hidden = False   '   Show just the budgtcomm rows, 139, 143, and 145 - 146
'
    Else
        Selection.EntireRow.Hidden = False                  ' UnHide all the rows
    End If
End Sub

 

[jpeters7635]

Overall I'm trying to turn 2 buttons into one, and your code only got me to the budgetcomm view and would not unhide when clicking same button again (which the unhide/hide button does).
What I'm trying to do is to step the views of the rows within the section and I'll try to make it clearer:
First View has all rows hidden.

Step 1: Click the Unhide / Hide button so rows in the section unhide to show historical information. Currently, the unhide / hide button is working fine to hide and unhide the seciton but I have to have a second button to collapse to just the budget + comment view).

I want to be able to click the Unhide / Hide button again and it goes to the budget + comment view (This is currently the second macro and requires the second button.

Click again and it unhide back to historical view.
Click again and it collapses the entire section back to view one.

Hope this makes more sense.

 

[johnnyL]

Overall I'm trying to turn 2 buttons into one, and your code only got me to the budgetcomm view and would not unhide when clicking same button again (which the unhide/hide button does).

Your original post said that you want to go from all rows shown to just showing the budgetcomm view. You said the code I offered did that. Your original post said step two was to hide entire section. So why are you now saying unhide is the second step? Am I misunderstanding your original post?

 

[johnnyL]

Ok going off of these assumptions:

Starting From All Hidden:
Click 1 will show all rows
Click 2 will show just budgetcomm
click 3 will show all rows
click 4 will hide all rows

This is the code I came up with to do that:

Private Sub CommandButton1_Click()
'
    Rows("139:146").Select
'
    If CommandButton1.caption = "Show/Hide 1" Then          ' If all rows are hidden then ...
        Selection.EntireRow.Hidden = False                  ' UnHide all the rows
        CommandButton1.caption = "Show/Hide 2"
'
    ElseIf CommandButton1.caption = "Show/Hide 2" Then      ' If we have gone from all hidden rows to showing all rows then ..
        Range("140:142, 144:144").EntireRow.Hidden = True   '   Hide all but the budgtcomm rows, 139, 143, and 145 - 146
        CommandButton1.caption = "Show/Hide 3"
'
    ElseIf CommandButton1.caption = "Show/Hide 3" Then      ' If only budgtcomm rows are showing then ...
        Selection.EntireRow.Hidden = False                  ' UnHide all the rows
        CommandButton1.caption = "Show/Hide 4"
'
    Else
        Selection.EntireRow.Hidden = True                   '   Hide all the rows
        CommandButton1.caption = "Show/Hide 1"
    End If
End Sub

 

[johnnyL]

Here it is with more meaningful names:

Private Sub CommandButton1_Click()
'
    Rows("139:146").Select
'
    If CommandButton1.caption = "UnHide All" Then               ' If all rows are hidden then ...
        Selection.EntireRow.Hidden = False                      '   UnHide all the rows
        CommandButton1.caption = "Show Just BudgetComm"
'
    ElseIf CommandButton1.caption = "Show Just BudgetComm" Then ' If we have gone from all hidden rows to showing all rows then ..
        Range("140:142, 144:144").EntireRow.Hidden = True       '   Hide all but the budgtcomm rows, 139, 143, and 145 - 146
        CommandButton1.caption = "UnHide All Again"
'
    ElseIf CommandButton1.caption = "UnHide All Again" Then     ' If only budgtcomm rows are showing then ...
        Selection.EntireRow.Hidden = False                      '   UnHide all the rows
        CommandButton1.caption = "Hide All"
'
    Else
        Selection.EntireRow.Hidden = True                       ' Hide all the rows
        CommandButton1.caption = "UnHide All"
    End If
End Sub

In case it needs to be said, this is code for the 'CommandButton1'
Adjust accordingly if need be.

 

[jpeters7635]

Here it is with more meaningful names:

Private Sub CommandButton1_Click()
'
    Rows("139:146").Select
'
    If CommandButton1.caption = "UnHide All" Then               ' If all rows are hidden then ...
        Selection.EntireRow.Hidden = False                      '   UnHide all the rows
        CommandButton1.caption = "Show Just BudgetComm"
'
    ElseIf CommandButton1.caption = "Show Just BudgetComm" Then ' If we have gone from all hidden rows to showing all rows then ..
        Range("140:142, 144:144").EntireRow.Hidden = True       '   Hide all but the budgtcomm rows, 139, 143, and 145 - 146
        CommandButton1.caption = "UnHide All Again"
'
    ElseIf CommandButton1.caption = "UnHide All Again" Then     ' If only budgtcomm rows are showing then ...
        Selection.EntireRow.Hidden = False                      '   UnHide all the rows
        CommandButton1.caption = "Hide All"
'
    Else
        Selection.EntireRow.Hidden = True                       ' Hide all the rows
        CommandButton1.caption = "UnHide All"
    End If
End Sub

In case it needs to be said, this is code for the 'CommandButton1'
Adjust accordingly if need be.

I'll check it out. Just concerned because the buttons in my workbook are just form control buttons, not active controls. Do you think it's easier to use active form controls?

 

[johnnyL]

So you are saying you have 'buttons'. Is the button that you are using 'button 1' or a different button?

 

[johnnyL]

It is definately easier to use CommandButtons

 

[dmt32]

Hi,

Try following & see if does what you want

Not sure fully understood but have only coded the sequence on a Tri-state

1 – Budget + Comment

2 – Comment

3 – Hide All

Sub show_hide()
 Static State As Integer
 
  State = IIf(State = xlVisible, xlHidden, IIf(State = xlHidden, xlAll, xlVisible))
    Rows("139:146").EntireRow.Hidden = State = xlHidden
    Range("140:142,144:144").EntireRow.Hidden = State = xlHidden Or Not State = xlAll
   
    ActiveSheet.Buttons(Application.Caller).Caption = IIf(State = xlHidden, "Budget + Comment", _
                                                        IIf(State = xlVisible, "Hide All", "Comment"))
 
End Sub

Code should work with your existing Forms Control button

Dave