How can we lock the entire worksheet without protecting using VBA code

Question

0.00/5 (No votes)

See more:

Not looking for Password Protection or event .Is there any method like public sub Protect()
under this we can do it .Not in event .

What I have tried:

Public Sub Worksheet_SelectionChange()
Dim xRg As Range
Dim xr As Range
Dim xRgEx As Range
Dim xRgExEach As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xr = Sheets("Sheet1").Range("A:N,1:15")
Set xRg = Sheets("Sheet1").Range("M:Z,15:30") 'Change the row range and column range you will lock without protecting worksheet
Set xRgEx = Application.Intersect(xr, xRg)
If xRgEx Is Nothing Then Exit Sub
Cells(1, 1).Select 'Specify a cell you will shift to after selecting the locked cells
Application.ScreenUpdating = False
End Sub

but My excel range is L18 to AI 50

Posted 27-May-22 5:05am

Thirumadhi T Johnson

Updated 27-May-22 5:45am

Add a Solution

Comments

Richard MacCutchan 27-May-22 11:23am

Select the correct range first, then use rng.Locked = True.

Thirumadhi T Johnson 27-May-22 14:57pm

Public Sub hi()
Dim xRg As Range
Dim xr As Range
Dim xRgEx As Range
Dim xRgExEach As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xr = Sheets("Sheet1").Range("A:N,1:15")
xr.Locked = True
'Set xRg = Sheets("Sheet1").Range("M:Z,15:30") 'Change the row range and column range you will lock without protecting worksheet
'Set xRgEx = Application.Intersect(xr, xRg)
'If xRgEx Is Nothing Then Exit Sub
'Cells(1, 1).Select 'Specify a cell you will shift to after selecting the locked cells
Application.ScreenUpdating = False
End Sub

Thirumadhi T Johnson 27-May-22 14:57pm

I tried this way but nothing is getting locked @Richard my task is to lock the entire worksheet if it unprotected mode too but not through any event but calling any method such as public sub Protectcell() like this .

Richard MacCutchan 28-May-22 3:55am

See the link I posted in my solution below. It explains how to protect a complete worksheet.

Thirumadhi T Johnson 30-May-22 3:48am

but Richard if I click on Unprotect option in excel it allows the users to edit again no matter if cell is locked too the same cell we can do anything it is unprotected sheet.

Richard MacCutchan 30-May-22 4:07am

Then you need to password protect it. You can only do what Excel allows.

Thirumadhi T Johnson 30-May-22 6:20am

hmm That is the Challenge because they are not looking for Password Protection

Richard MacCutchan 30-May-22 6:57am

As I say, you can only use what Excel allows.

Thirumadhi T Johnson 30-May-22 7:00am

such as any example @Richard

Richard MacCutchan 30-May-22 7:08am

I already explained the two options you have.

Thirumadhi T Johnson 30-May-22 8:16am

Ohk Thank You

matblue25 27-May-22 16:19pm

It is not clear what you're trying to accomplish. It seems like you're trying to lock either some or all of the cells in the worksheet if the worksheet is not protected. And to do it in a Sub that is not an event handler Sub. Is that correct? If not, please provide a complete and detailed explanation of what you're trying to do.

Thirumadhi T Johnson 30-May-22 3:41am

Yes u r right @matblue25 ,I want to do in a Sub not in event handler .Password Protecting is also not a requirement. So for specific users the sheets should be enable for everything and rest of the users only can see like read only mode ,if we click on option Unprotect Sheet also ,the same specific users should not be able to do anything rather than only seeing. Or else is it Possible to specify 5/6 Users name in event handler for specific sheet that they can have all the rights to perform .

Thirumadhi T Johnson 30-May-22 3:44am

So Here I am looking for both ways either in event handler we can mention few users name who can do delete,insert or edit in specific sheet or in Sub by doing the same thing .I hope now it is clear .Looking forward for help please

matblue25 30-May-22 15:01pm

At this point, I have no idea what you are trying to do. The only thing your code does that matches anything you've said is that it's in a Sub rather than an event procedure. There's nothing about protecting the worksheet or letting particular users have different options than other users.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2022-05-27T05:45:00

Solution 1

See Protect a worksheet using VBA[^].

Posted 27-May-22 5:45am

Richard MacCutchan