how to protect sheets vba code

A

AYSHANA

Board Regular
Joined
Oct 16, 2021
Messages
90
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
good day
I would like to ask to to protect my sheet

I have tried to put ActiveSheet.protect ("1") but unfortunately, im getting a popup message ( the cell or chart you're trying to change is a protected sheet. To make a change unprotect the sheet. You might be requested to enter a password.)

thank you.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Result, Test As Range
Set Result = Range("N5")
ActiveSheet.Unprotect ("1")
Select Case Result
Case Is = "Reagent & Ortho Cards File": Rows("12:100").EntireRow.Hidden = True

Rows("7:11").EntireRow.Hidden = False
ActiveSheet.Protect ("1")

Case Is = "Validation File": Rows("7:11").EntireRow.Hidden = True

Rows("18:100").EntireRow.Hidden = True
Rows("12:17").EntireRow.Hidden = False

ActiveSheet.Protect ("1")
Case Is = "Antibody Workup & Transfusion Reaction Database": Rows("7:17").EntireRow.Hidden = True

Rows("24:100").EntireRow.Hidden = True
Rows("18:23").EntireRow.Hidden = False
ActiveSheet.Protect ("1")


Case Is = "QC Files": Rows("7:23").EntireRow.Hidden = True

Rows("29:100").EntireRow.Hidden = True
Rows("24:28").EntireRow.Hidden = False
ActiveSheet.Protect ("1")



Case Is = "Received Blood Components": Rows("7:29").EntireRow.Hidden = True

Rows("35:100").EntireRow.Hidden = True
Rows("30:34").EntireRow.Hidden = False
ActiveSheet.Protect ("1")


Case Is = "Equipment Calibration File": Rows("7:35").EntireRow.Hidden = True

Rows("48:100").EntireRow.Hidden = True
Rows("36:47").EntireRow.Hidden = False

ActiveSheet.Protect ("1")

Case Is = "COMARK File": Rows("7:48").EntireRow.Hidden = True

Rows("54:100").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = False


Case Is = "CAP Inspection File": Rows("7:54").EntireRow.Hidden = True

Rows("60:100").EntireRow.Hidden = True
Rows("55:59").EntireRow.Hidden = False
ActiveSheet.Protect ("1")


Case Is = "BB Soft Copy Files": Rows("7:60").EntireRow.Hidden = True

Rows("71:100").EntireRow.Hidden = True
Rows("61:70").EntireRow.Hidden = False

ActiveSheet.Protect ("1")

Case Is = "KPI Files": Rows("7:71").EntireRow.Hidden = True

Rows("75:100").EntireRow.Hidden = True
Rows("72:74").EntireRow.Hidden = False
ActiveSheet.Protect ("1")


Case Is = "Temperatuer Files": Rows("7:75").EntireRow.Hidden = True

Rows("80:100").EntireRow.Hidden = True
Rows("76:79").EntireRow.Hidden = False

ActiveSheet.Protect ("1")


Case Is = "Administrative Files": Rows("7:80").EntireRow.Hidden = True

Rows("88:100").EntireRow.Hidden = True
Rows("81:87").EntireRow.Hidden = False
ActiveSheet.Protect ("1")


End Select


End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
I am not sure why you are getting this error. What line of code raises the error?

Just unprotect the sheet once at the beginning, and protect it at the end.

Because you are in the module for the sheet, omit ActiveSheet.

It hampers readability to put a ":" to add a new statement on the same line of a Case option when there are more lines following it.

You are declaring Result as Variant (allowing it to default) but you are using it as Range. Better to declare it as Range.

You are declaring but not using Test.

When posting code, your code will be more readable if you mark it with code tags.

Here is a cleaned up version, but I'm still not sure why you are getting the error.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Result As Range
   Set Result = Range("N5")
  
   Unprotect "1"
  
   Select Case Result
  
      Case Is = "Reagent & Ortho Cards File"

         Rows("12:100").EntireRow.Hidden = True
         Rows("7:11").EntireRow.Hidden = False
     
      Case Is = "Validation File"

         Rows("7:11").EntireRow.Hidden = True
         Rows("18:100").EntireRow.Hidden = True
         Rows("12:17").EntireRow.Hidden = False
             
      Case Is = "Antibody Workup & Transfusion Reaction Database"

         Rows("7:17").EntireRow.Hidden = True
         Rows("24:100").EntireRow.Hidden = True
         Rows("18:23").EntireRow.Hidden = False
     
      Case Is = "QC Files"

         Rows("7:23").EntireRow.Hidden = True
         Rows("29:100").EntireRow.Hidden = True
         Rows("24:28").EntireRow.Hidden = False
     
      Case Is = "Received Blood Components"

         Rows("7:29").EntireRow.Hidden = True
         Rows("35:100").EntireRow.Hidden = True
         Rows("30:34").EntireRow.Hidden = False
     
      Case Is = "Equipment Calibration File"

         Rows("7:35").EntireRow.Hidden = True
         Rows("48:100").EntireRow.Hidden = True
         Rows("36:47").EntireRow.Hidden = False
     
      Case Is = "COMARK File"

         Rows("7:48").EntireRow.Hidden = True
         Rows("54:100").EntireRow.Hidden = True
         Rows("49:53").EntireRow.Hidden = False
     
      Case Is = "CAP Inspection File"

         Rows("7:54").EntireRow.Hidden = True
         Rows("60:100").EntireRow.Hidden = True
         Rows("55:59").EntireRow.Hidden = False
     
      Case Is = "BB Soft Copy Files"

         Rows("7:60").EntireRow.Hidden = True
         Rows("71:100").EntireRow.Hidden = True
         Rows("61:70").EntireRow.Hidden = False
     
      Case Is = "KPI Files"

         Rows("7:71").EntireRow.Hidden = True
         Rows("75:100").EntireRow.Hidden = True
         Rows("72:74").EntireRow.Hidden = False
     
      Case Is = "Temperatuer Files"

         Rows("7:75").EntireRow.Hidden = True
         Rows("80:100").EntireRow.Hidden = True
         Rows("76:79").EntireRow.Hidden = False
        
      Case Is = "Administrative Files"

         Rows("7:80").EntireRow.Hidden = True
         Rows("88:100").EntireRow.Hidden = True
         Rows("81:87").EntireRow.Hidden = False
  
   End Select
  
   Protect "1"


End Sub
 
Upvote 0
this is my code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Result, Test As Range
Set Result = Range("N5")

Select Case Result
Case Is = "Reagent & Ortho Cards File": Rows("12:100").EntireRow.Hidden = True

Rows("7:11").EntireRow.Hidden = False



Case Is = "Validation File": Rows("7:11").EntireRow.Hidden = True

Rows("18:100").EntireRow.Hidden = True
Rows("12:17").EntireRow.Hidden = False


Case Is = "Antibody Workup & Transfusion Reaction Database": Rows("7:17").EntireRow.Hidden = True

Rows("24:100").EntireRow.Hidden = True
Rows("18:23").EntireRow.Hidden = False


Case Is = "QC Files": Rows("7:23").EntireRow.Hidden = True

Rows("29:100").EntireRow.Hidden = True
Rows("24:28").EntireRow.Hidden = False



Case Is = "Received Blood Components": Rows("7:29").EntireRow.Hidden = True

Rows("35:100").EntireRow.Hidden = True
Rows("30:34").EntireRow.Hidden = False


Case Is = "Equipment Calibration File": Rows("7:35").EntireRow.Hidden = True

Rows("48:100").EntireRow.Hidden = True
Rows("36:47").EntireRow.Hidden = False

Case Is = "COMARK File": Rows("7:48").EntireRow.Hidden = True

Rows("54:100").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = False


Case Is = "CAP Inspection File": Rows("7:54").EntireRow.Hidden = True

Rows("60:100").EntireRow.Hidden = True
Rows("55:59").EntireRow.Hidden = False


Case Is = "BB Soft Copy Files": Rows("7:60").EntireRow.Hidden = True

Rows("71:100").EntireRow.Hidden = True
Rows("61:70").EntireRow.Hidden = False



Case Is = "KPI Files": Rows("7:71").EntireRow.Hidden = True

Rows("75:100").EntireRow.Hidden = True
Rows("72:74").EntireRow.Hidden = False


Case Is = "Temperatuer Files": Rows("7:75").EntireRow.Hidden = True

Rows("80:100").EntireRow.Hidden = True
Rows("76:79").EntireRow.Hidden = False



Case Is = "Administrative Files": Rows("7:80").EntireRow.Hidden = True

Rows("88:100").EntireRow.Hidden = True
Rows("81:87").EntireRow.Hidden = False



End Select



End Sub




VBA Code:
 
Upvote 0
I asked which line of code is causing the error, and you haven't answered that.

Also, although the board does not require code tags, I can't wade through code that is pasted in without formatting.
 
Upvote 0
6StringJazzer said:
I asked which line of code is causing the error, and you haven't answered that.

Also, although the board does not require code tags, I can't wade through code that is pasted in without formatting.
Click to expand...
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Result, Test As Range
Set Result = Range("N5")

Select Case Result
    Case Is = "Reagent & Ortho Cards File": Rows("12:100").EntireRow.Hidden = True

    Rows("7:11").EntireRow.Hidden = False
    


    Case Is = "Validation File": Rows("7:11").EntireRow.Hidden = True

     Rows("18:100").EntireRow.Hidden = True
     Rows("12:17").EntireRow.Hidden = False
     
     
    Case Is = "Antibody Workup & Transfusion Reaction Database": Rows("7:17").EntireRow.Hidden = True

     Rows("24:100").EntireRow.Hidden = True
     Rows("18:23").EntireRow.Hidden = False


Case Is = "QC Files": Rows("7:23").EntireRow.Hidden = True

     Rows("29:100").EntireRow.Hidden = True
     Rows("24:28").EntireRow.Hidden = False



Case Is = "Received Blood Components": Rows("7:29").EntireRow.Hidden = True

     Rows("35:100").EntireRow.Hidden = True
     Rows("30:34").EntireRow.Hidden = False


Case Is = "Equipment Calibration File": Rows("7:35").EntireRow.Hidden = True

     Rows("48:100").EntireRow.Hidden = True
     Rows("36:47").EntireRow.Hidden = False

Case Is = "COMARK File": Rows("7:48").EntireRow.Hidden = True

     Rows("54:100").EntireRow.Hidden = True
     Rows("49:53").EntireRow.Hidden = False


Case Is = "CAP Inspection File": Rows("7:54").EntireRow.Hidden = True

     Rows("60:100").EntireRow.Hidden = True
     Rows("55:59").EntireRow.Hidden = False


Case Is = "BB Soft Copy Files": Rows("7:60").EntireRow.Hidden = True

     Rows("71:100").EntireRow.Hidden = True
     Rows("61:70").EntireRow.Hidden = False



Case Is = "KPI Files": Rows("7:71").EntireRow.Hidden = True

     Rows("75:100").EntireRow.Hidden = True
     Rows("72:74").EntireRow.Hidden = False


Case Is = "Temperatuer Files": Rows("7:75").EntireRow.Hidden = True

     Rows("80:100").EntireRow.Hidden = True
     Rows("76:79").EntireRow.Hidden = False



Case Is = "Administrative Files": Rows("7:80").EntireRow.Hidden = True

     Rows("88:100").EntireRow.Hidden = True
     Rows("81:87").EntireRow.Hidden = False



     End Select



End Sub
 
Upvote 0
6StringJazzer said:
I asked which line of code is causing the error, and you haven't answered that.

Also, although the board does not require code tags, I can't wade through code that is pasted in without formatting.
Click to expand...
i have tried to put activesheet, but still same problem
kindly refer to attachment
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Result, Test As Range
Set Result = Range("N5")
ActiveSheet.Unprotect "1"

Select Case Result
    Case Is = "Reagent & Ortho Cards File": Rows("12:100").EntireRow.Hidden = True

    Rows("7:11").EntireRow.Hidden = False
    


    Case Is = "Validation File": Rows("7:11").EntireRow.Hidden = True

     Rows("18:100").EntireRow.Hidden = True
     Rows("12:17").EntireRow.Hidden = False
     
     
    Case Is = "Antibody Workup & Transfusion Reaction Database": Rows("7:17").EntireRow.Hidden = True

     Rows("24:100").EntireRow.Hidden = True
     Rows("18:23").EntireRow.Hidden = False


Case Is = "QC Files": Rows("7:23").EntireRow.Hidden = True

     Rows("29:100").EntireRow.Hidden = True
     Rows("24:28").EntireRow.Hidden = False



Case Is = "Received Blood Components": Rows("7:29").EntireRow.Hidden = True

     Rows("35:100").EntireRow.Hidden = True
     Rows("30:34").EntireRow.Hidden = False


Case Is = "Equipment Calibration File": Rows("7:35").EntireRow.Hidden = True

     Rows("48:100").EntireRow.Hidden = True
     Rows("36:47").EntireRow.Hidden = False

Case Is = "COMARK File": Rows("7:48").EntireRow.Hidden = True

     Rows("54:100").EntireRow.Hidden = True
     Rows("49:53").EntireRow.Hidden = False


Case Is = "CAP Inspection File": Rows("7:54").EntireRow.Hidden = True

     Rows("60:100").EntireRow.Hidden = True
     Rows("55:59").EntireRow.Hidden = False


Case Is = "BB Soft Copy Files": Rows("7:60").EntireRow.Hidden = True

     Rows("71:100").EntireRow.Hidden = True
     Rows("61:70").EntireRow.Hidden = False



Case Is = "KPI Files": Rows("7:71").EntireRow.Hidden = True

     Rows("75:100").EntireRow.Hidden = True
     Rows("72:74").EntireRow.Hidden = False


Case Is = "Temperatuer Files": Rows("7:75").EntireRow.Hidden = True

     Rows("80:100").EntireRow.Hidden = True
     Rows("76:79").EntireRow.Hidden = False



Case Is = "Administrative Files": Rows("7:80").EntireRow.Hidden = True

     Rows("88:100").EntireRow.Hidden = True
     Rows("81:87").EntireRow.Hidden = False



     End Select

ActiveSheet.Protect "1"

End Sub



error.png
 
Upvote 0
Is your sheet unprotecting when you do it manually with 1 as the password?
and we still need to know what line of the code is causing the error
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Using a cell on another worksheet to run my code?
Replies
1
Views
392
rlv01
R
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
L
  • Solved
How to hide rows on another sheet with VBA?
Replies
3
Views
717
dmt32
D
R
  • Solved
VBA Code for hiding Rows, issues with subsets
Replies
9
Views
900
kevin9999
K
D
  • Solved
Hiding rows with multiple dropdown menu's
Replies
6
Views
1K
kevin9999
K

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top