Need multiple buttons to hide/show rows on another tab

[mjtrifillis]

Thanks for reading! As show above, I need to create multiple (activex) buttons each hiding or showing a specific range of rows on another page but am struggling to find a solution. I have messed with this a little in the past but found my answers online and modified them to suit my needs.
Here is what I want to do:
click a button labeled YES to unhide rows 1:2 & 8:14 while keeping rows 3:7 hidden, the button unclicked or clicked off would hide rows 1:14 all on a sheet labeled Existing Leads
click a button labeled YES to unhide rows 15:16 & 22:28 while keeping rows 17:21 hidden, the button unclicked or clicked off would hide rows 15:28 all on a sheet labeled Existing Leads
repeat for hundreds of rows.

ideally there would be a way to copy/paste so that i dont have to alter the code on each button but if thats the answer then so be it...open to suggestions, but i would rather have a button than a check box solution.

i would also like to keep the "Existing Leads" sheet protected if that makes any difference.

Thanks,
Mikey

 

[skillilea]

Try this. I'd use shapes. Put two shapes on a sheet name them btn1, btn2.
Assign the below macro to each of them.

Const ProtectionPassword As String = "YourPassword"

Public Sub HandleHideRows()
    Dim btn As Shape
    Dim ws As Worksheet
    Set ws = Sheets("ExistingLeads")
    
    Set btn = ActiveSheet.Shapes(Application.Caller)   ' change to wherever you want to run
    
    Application.ScreenUpdating = False
    
    UnprotectSheet ws
    
    If btn.Name = "btn1" Then
        If btn.TextFrame.Characters.Text = "YES" Then
            ws.Rows("1:2").Hidden = False
            ws.Rows("8:14").Hidden = False
            ws.Rows("3:7").Hidden = True
            btn.TextFrame.Characters.Text = "NO"
        Else
            ws.Rows("1:14").Hidden = True
            btn.TextFrame.Characters.Text = "YES"
        End If
    End If
    
    If btn.Name = "btn2" Then
        If btn.TextFrame.Characters.Text = "YES" Then
            ws.Rows("1:2").Hidden = False
            ws.Rows("8:14").Hidden = False
            ws.Rows("3:7").Hidden = True
            btn.TextFrame.Characters.Text = "NO"
        Else
            ws.Rows("1:14").Hidden = True
            btn.TextFrame.Characters.Text = "YES"
        End If
    End If
    
    ProtectSheet ws
    
    Application.ScreenUpdating = True
End Sub

Private Sub ProtectSheet(Optional wsIn As Worksheet)
' protect the sheet

    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    If Not wsIn Is Nothing Then
        Set ws = wsIn
    End If
    
    If Not ws.ProtectContents Then
        ws.Protect DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=True, _
            Password:=ProtectionPassword
            
            ws.EnableSelection = xlUnlockedCells
    End If
End Sub

Private Sub UnprotectSheet(Optional wsIn As Worksheet)
' unprotect the sheet

    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    If Not wsIn Is Nothing Then
        Set ws = wsIn
    End If
    
    ws.Unprotect Password:=ProtectionPassword
End Sub

 

[mjtrifillis]

did not work, i created 2 shapes, copied the code into a macro, asigned the macro to each button and nothing changed. the only edits i made was to add a space on the sheet name (your code didn't have the space) and changed the password in both areas.

 

[mjtrifillis]

did not work, i created 2 shapes, copied the code into a macro, asigned the macro to each button and nothing changed. the only edits i made was to add a space on the sheet name (your code didn't have the space) and changed the password in both areas..... it does however lock the other tab.

 

[skillilea]

Did you name the shapes? "btn1", "btn2" Make sure you see the name in the highlight.

 

[mjtrifillis]

i solved it by combining two methods together which works, i cannot figure out how to unlock/ lock the sheet when its protected.
here's the code:
Sub btn01_Click()
If Worksheets("Existing Leads").Rows("1:14").Hidden = True Then
Sheets("Existing Leads").Rows("1:2").EntireRow.Hidden = False
Sheets("Existing Leads").Rows("3:7").EntireRow.Hidden = True
Sheets("Existing Leads").Rows("8:14").EntireRow.Hidden = False
Else
Sheets("Existing Leads").Rows("1:14").EntireRow.Hidden = True
End If
End Sub

Sub btn02_Click()
If Worksheets("Existing Leads").Rows("15:28").Hidden = True Then
Sheets("Existing Leads").Rows("15:16").EntireRow.Hidden = False
Sheets("Existing Leads").Rows("17:21").EntireRow.Hidden = True
Sheets("Existing Leads").Rows("22:28").EntireRow.Hidden = False
Else
Sheets("Existing Leads").Rows("15:28").EntireRow.Hidden = True
End If
End Sub

 

[skillilea]

All the code to do this is post #2, what did not work?

 

[mjtrifillis]

The code in #2 locked/unlocked the other tab but did not hide or unhide rows

 

[skillilea]

Did you name the shapes? "btn1", "btn2" Make sure you see the name in the highlight.

Set ws = Sheets("Existing Leads")

If the worksheet is protected/unprotected the rows will hide based on the button click.