My macro will not run b/c the sheet is protected

[sneel3]

how can I have the macro turn off the protection at the beginning and turn it back on at the end?

 

[stuartford]

joe thanks for your help but maybe im not making myself clear

i understand that my syntax is incorrect
But it still protects the sheet using a password when i use this code
ActiveSheet.Protect password = "stu"

my question is
if im using the wrong syntax and its still working does anyone know what its actually doing and what is it using to decide what the password should be?

it was more a general question than an issue i am having
Thanks
Stu

 

[sneel3]

Don't forget about my question.

Scott

 

[stuartford]

sorry sneel, im post stealing here

Apologies

Stu

 

[Joe Was]

ActiveSheet.Protect password = "stu"
is the same as:

ActiveSheet.Protect

AKA: Protect without the use of a password!

To protect with a password you must use the correct syntax:

ActiveSheet.Protect password: = "stu"

 

[sneel3]

Joe, any idea why mine isn't working?

 

[Joe Was]

When you record steps the code writer records every step as it happens. It does not check to make sure it is logical. So, if you scroll right some and stop and look at the sheet and scroll again, after seeing that where you needed to go is up/down some the the recorder has two scroll ranges one pick-up after the other.

In your case:

ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("L11:P65536")
ActiveSheet.Protection.AllowEditRanges.Add Title:="Range2", Range:=Range("U11:Z65536")

And probably the statement before this?
Are chopped up ranges, try something like:

ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Range("L11:P65536", "U11:Z65536")

The Range may also need a sheet reference, like:

ActiveSheet.Range("L11:P65536", "U11:Z65536")
or
Sheets("Sheet1").Range("L11:P65536", "U11:Z65536")

 

[sneel3]

I still can't get it to work. Any other ideas???

 

[Joe Was]

You should not need to protect ranges or not protect other ranges by code.

From the Excel ToolBar: Format - Cell - Protection [as a TAB]
each cell can be locked or unlocked.

When you protect a sheet only the ones that have the locked check box clicked will be protected. The options on the protect screen generally deal with the cells formatted as un-locked. And, the default lock state for the default cell is: LOCKED!

You need to see if the range of cells that get protected/Locked is a smaller number of cells than thouse that do get protected/locked?

If the cells that get protected are just a few you can run a loop to unprotect a large range or even the whole sheet, then Lock just the cells you need protected by code. If the number that gets un-locked is just a few then depending on the range or pattern of un-locked cells you will need to write code to deal with just unlocking thouse cells.

Or, do what most people do manually unlock the cells you need un locked and then add generic Protect and UnProtect code to the begining and ending of your code!

This is a trick, you can protect and unprotect based upon which cells the user selects, independent of other code!

Here I check for the active range if it is the cells I need protected than the sheet is protected, all cells!

If the cells selected are the ones I want unprotected then UnProtect the whole sheet!

This way I do not need to Format the cell as Locked or Unlocked when I protect and Un-Protect!


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code, like: Sheet1!

If ((Target.Row >= 12 And Target.Row <= 64) And _
(Target.Column >= 3 And Target.Column <= 8)) Then

ActiveSheet.Unprotect Password:="admin"
Else

ActiveSheet.Protect Password:="admin"

MsgBox "You can only edit cells in Range:" & vbLf & vbLf & _
"C12:H64" & vbLf & vbLf & " Your selection: " & Target.Address & _
vbLf & "is not in the Edit Range!"
End If
End Sub

 

[stuartford]

joe have you tested what im saying????

cause as a quote from you

ActiveSheet.Protect password = "stu"
is the same as:

ActiveSheet.Protect

it isnt the same at all
cause
ActiveSheet.Protect
wouldnt want a password to unprotect

but


ActiveSheet.Protect password = "stu"

does expect a password to unprotect

ande the password isnt "stu"

hey hey

Stu

 

[Joe Was]

Yes you are right I just assumed that it would not take incorrect syntax, but it did. And, probably like you I tried evey combinaltion I could think of and it will not work:
Blank
stu
=stu
blank stu
blank = stu
Password = stu
Password stu
password ="stu"
Blank="stu"

and others.
So, this is a new one for me!
Sorry, I have no solution, that we can post.