How to protect different cells using VBA

alinpion

Board Regular
Joined
Jul 12, 2011
Messages
79
Hello!

I want to protect different cells from one of my sheets (tab) and the VBA code that I have is not working

Sub ProtectTheSheet()
Cells.Locked = False
Range("A2:A100,C2:C100,D2:D100,J2:J100,K2:K100").Locked = True
ActiveSheet.Protect
End Sub

Can you tell me where is the error!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Doesn't give any error just doesn't work.

The entire VBA code of the sheet is this:

Private Sub Worksheet_Activate()
Dim pwd As String: pwd = "password"
Cells(Rows.Count, Columns.Count).Activate
If Application.InputBox("Type the password", "Access Password", "*******") <> pwd Then
Sheets("Sheet1").Activate
MsgBox "Wrong Password"
Else
Range("A1").Activate
End If
End Sub


Sub ProtectTheSheet()
Cells.Locked = False
Range("A2:A100,C2:C100,D2:D100,J2:J100,K2:K100").Locked = True
ActiveSheet.Protect
End Sub
 
Upvote 0
You need to call your ProtectTheSheet sub routine in your Worksheet activate event code.

Code:
If Application.InputBox("Type the password", "Access Password", "*******") <> pwd Then
    Sheets("Sheet1").Activate
    MsgBox "Wrong Password"
Else
    Call ProtectTheSheet
    Range("A1").Activate
End If
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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