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]

No answer about if you are using 'Button 1' or not so here is the code that you could use for 'Button 1' :

Sub show_hide()
'
    If ActiveSheet.Buttons("Button 1").Text = "UnHide All" Then                 ' If all rows are hidden then ...
        Range("139:146").EntireRow.Hidden = False                               '   UnHide all the rows
        ActiveSheet.Buttons("Button 1").Text = "Show Just BudgetComm"
'
    ElseIf ActiveSheet.Buttons("Button 1").Text = "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
        ActiveSheet.Buttons("Button 1").Text = "UnHide All Again"
'
    ElseIf ActiveSheet.Buttons("Button 1").Text = "UnHide All Again" Then       ' If only budgtcomm rows are showing then ...
        Range("139:146").EntireRow.Hidden = False                               '   UnHide all the rows
        ActiveSheet.Buttons("Button 1").Text = "Hide All"
'
    Else
        Range("139:146").EntireRow.Hidden = True                                ' Hide all the rows
        ActiveSheet.Buttons("Button 1").Text = "UnHide All"
    End If
End Sub

If you are using a different 'Button' # then just substitute the correct number in the code in place of '1'

 

[jpeters7635]

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

You're a genius! That did exactly what I needed it to do. I know you didn't understand, but the reason was that I can combine 2 buttons into one which cleans up the entire workbook!

 

[johnnyL]

FYI @jpeters7635, the checkmark for a solution, most of the time, should not be on your own post. The checkmark normally goes on the post # that solved your question from the original post. The check mark is for future readers that want to see what solved the original question. In this thread, your post that you checked did not solve the original question, therefor, your post should not be marked as the solution.

 

[dmt32]

You're a genius! That did exactly what I needed it to do. I know you didn't understand, but the reason was that I can combine 2 buttons into one which cleans up the entire workbook!

What I was not sure about was the sequencing you wanted but just guessed it to be three states.
If code is doing what you want then may want to consider updated version below - There should be no difference in what code does just this version uses Constants that more reflect your activity and hopefully, makes it a little easier to read.

Sub show_hide()
    Dim m           As Variant
    Static State    As Integer

 
    State = IIf(State = xlBoth, xlHidden, IIf(State = xlHidden, xlComments, xlBoth))
    m = Application.Match(State, Array(xlHidden, xlComments, xlBoth), 0)
    ActiveSheet.Buttons(Application.Caller).Caption = Choose(m, "Budget + Comment", "Comment", "Hide All")
    
    Rows("139:146").EntireRow.Hidden = State = xlHidden
    Range("140:142,144:144").EntireRow.Hidden = State = xlHidden Or Not State = xlComments
    
End Sub

Many thanks for your feedback & glad we were able to help.

Dave

 

[jpeters7635]

So I have a issue when protection for which I found a workaround, but wondering if you have another idea. After protecting the sheet, because the name changes on the button (which is spectacular), the debugger throws up a runtime error.

I selected the "edit objects" option in the protect sheets to bypass - but do you have a recommendation of what to do so the user can't really click and edit the button? Not that it's a major issue because the code reverts it back. Just curious.

Thank you so much!

 

[johnnyL]

Try putting
On Error Resume Next
Right before that line


If that doesn't work you could unprotect right before that line and then protect right after it.

 

[dmt32]

So I have a issue when protection for which I found a workaround, but wondering if you have another idea.

Try unprotecting the sheet before code runs & then re-protect after

Sub show_hide()
    Dim m           As Variant
    Static State    As Integer

    ActiveSheet.Unprotect Password:=""
    
    State = IIf(State = xlBoth, xlHidden, IIf(State = xlHidden, xlComments, xlBoth))
    m = Application.Match(State, Array(xlHidden, xlComments, xlBoth), 0)
    ActiveSheet.Buttons(Application.Caller).Caption = Choose(m, "Budget + Comment", "Comment", "Hide All")
    
    Rows("139:146").EntireRow.Hidden = State = xlHidden
    Range("140:142,144:144").EntireRow.Hidden = State = xlHidden Or Not State = xlComments
    
    ActiveSheet.Protect Password:=""
    
End Sub

add password as required

Dave