Protect/unprotect sheet when I click button

mare23

New Member
Joined
Oct 28, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I have a button "-" and "+" which kicks off a VBA method which either hides or unhides rows on a Worksheet.

I'd like to protect the worksheets but if I click 5th time (I have 5 groups of rows, for the firt 4 rows shows that the sheet is protected) on "+" it shows me unprotected sheet but I want to protect sheet, or when I click a button "-" 1st time and all other rows are hidden again it shows an unprotect sheet

I've tried various protection settings but I still get the same error.

Is there one I should have tried?

Any suggestions? Should I have VBA unprotect/protect the sheet?

Sub HideRws()
ActiveSheet.Unprotect 1234
If Rows("94").Hidden = False Then
Rows("94:109").Hidden = True
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("78").Hidden = False Then
Rows("78:93").Hidden = True
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("62").Hidden = False Then
Rows("62:77").Hidden = True
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("46").Hidden = False Then
Rows("46:61").Hidden = True
ActiveSheet.Protect 1234
End If
End Sub
Sub UnHideRws()
ActiveSheet.Unprotect 1234
If Rows("46").Hidden = True Then
Rows("46:61").Hidden = False
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("62").Hidden = True Then
Rows("62:77").Hidden = False
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("78").Hidden = True Then
Rows("78:93").Hidden = False
ActiveSheet.Protect 1234
Exit Sub
ActiveSheet.Unprotect 1234
ElseIf Rows("94").Hidden = True Then
Rows("94:109").Hidden = False
ActiveSheet.Protect 1234
End If
End Sub

Thx Mare
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
VBA Code:
Sub HideRws()
    ActiveSheet.Unprotect 1234
    If Rows("94").Hidden = False Then
        Rows("94:109").Hidden = True
    ElseIf Rows("78").Hidden = False Then
        Rows("78:93").Hidden = True
    ElseIf Rows("62").Hidden = False Then
        Rows("62:77").Hidden = True
    ElseIf Rows("46").Hidden = False Then
        Rows("46:61").Hidden = True
    End If
    ActiveSheet.Protect 1234
    
End Sub
 
Sub UnHideRws()
    ActiveSheet.Unprotect 1234
    If Rows("46").Hidden = True Then
        Rows("46:61").Hidden = False
    ElseIf Rows("62").Hidden = True Then
        Rows("62:77").Hidden = False
    ElseIf Rows("78").Hidden = True Then
        Rows("78:93").Hidden = False
    ElseIf Rows("94").Hidden = True Then
        Rows("94:109").Hidden = False
    End If
    ActiveSheet.Protect 1234
End Sub
 

Forum statistics

Threads
1,144,660
Messages
5,725,645
Members
422,634
Latest member
YenOlmedo

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