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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
The "error" message you display is not triggered by VBA! If you would use VBA to try to change the content of a cell on a protected worksheet, you would get a dialog like this:
1696258203289.png
 
Upvote 1
Glad you have it sorted, happy we could help
 
Upvote 1
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
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,674
Messages
6,126,140
Members
449,294
Latest member
Jitesh_Sharma

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