How do I determine if Excel worksheet Protected? 3

[ktwclark]

Hi

I use macros on an Excel worksheet and I also use protection of the cells. I can protect and unprotect no problem but I'm not sure how to determine if protection has been applied or not. If I apply protection to an already protected worksheet I get an error. I can workaround this using error codes, but there must be a way to determine if protection has been applied in the first place.

Cheers

KC

 

[faxof]

before your

ActiveSheet.Protect

line add the code

 IF ActiveSheet.Protect = False Then

faxof

 

[SteveBausch]

Just checked Help, you can apply protections to a protected sheet IF you include the correct password. I think the issue is password-related.

 

[ktwclark]

Sorry, but it's not as simple as that.

You should test what you say:

Put a command button on a worksheet and add this code to it:

Private Sub CommandButton1_Click()
'Focus must be on a worksheet cell, not command button,
'before messing with protection
Range("A1&quot.Select
If ActiveSheet.Protect = True Then MsgBox "Protected"
If ActiveSheet.Protect = False Then MsgBox "UnProtected"
End Sub

Apply and remove the worksheet protection manually and click the button. Everytime you click the button the worksheet protection is reapplied no matter what.

Not what I'm looking for and an incorrect answer too!

I've since discovered that you can apply and reapply (or remove and re-remove) the protection without getting any errors, so I'm a happy man.

However, curiosity still has the better of me!

Any more takers?

KC

 

[SteveBausch]

You are correct, I didn't test, I READ THE HELP FILE.

 

[GlennUK]

Change the part that says
If ActiveSheet.Protect

to be

If ActiveSheet.ProtectContents

which tests the protection like you want.

Glenn.

 

[SteveBausch]

FROM THE HELP FILE:
Protects a chart or worksheet (Syntax 1) or a workbook (Syntax 2) so that it cannot be modified.

Syntax 1

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

Syntax 2

expression.Protect(Password, Structure, Windows)


Yes, you can get a boolean returned, but the Help shows it as method that doesn't return a boolean.


Thanks to GlennUK. Glenn, I wasn't aware of the .ProtectContents property, thanks for pointing it out to me.
Steve

 

[KenWright]

In case it is of any use, the following are a couple of macros I picked up to do the same thing to a whole workbook:-

Public Sub ToggleProtect1()
Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub



Sub Toggleprotect2()
Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub

Downside if you protect/unprotect a single sheet manually is that this will reverse the protection status on each sheet so you still end up with a mix.

Upside to the first is that you actually get a status report on the protection status of each sheet at the end.

Regards
Ken...........

 

[ktwclark]

Activesheet.ProtectContents it is.

Thanks to all who replied.

I've learned something new and I hope you guys did too!

KC

 

[xlbo]

"Sorry, but it's not as simple as that.
You should test what you say:"
"Not what I'm looking for and an incorrect answer too!"

ktwclark - you should maybe polish up on your manners - no-one gets paid to post answers here so replies like that tends to get further requests for help ignored. If you know so much - whay ask the questions ???

oh and BTW - the way to say thanks round here is to award a star - I notice Geln has one for his answer but you didn't award it - stars are not only the way to say thanks but also point people that use the search functionality to questions that have received a good reply

You award a star by clicking on "Mark this post as a helpful / expert post" on the appropriate post Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ktwclark]

I didn't mean to cause offense and I appreciate this is an open forum. But every time I reply on this forum I get a message ticking me off.

By no means do I purport to "know so much". At no point did I claim this. The fact I asked a question should show this.

I simply asked a question and got a response that never solved my problem and was inaccurate. I was being told to use a "Method" as opposed to reading a "Property". - "Sorry but it's not as simple as that" - a fair comment I would have said.

"You should test what you say:" was in reply to faxof not SteveBausch and again I think it was a valid comment, although it could be applied to SteveBausch too. I appreciate the responses are others trying to help but it isn't very helpful when others make an educated guess (or reiterate the first thing they find within the Help files) at an answer without having previously used the piece of code they are posting. Especially when I'm being told to conditionally test a method not a property - Even the help files tell you it is a method.

I would have expected reponses to be from others that have been in the same situation and have found a solution, not from others that are simply responding to each and every question with their own take on things. Faxof said nothing like "You could try..." or "Perhaps this might work..." or "I haven't tried it yet but..." instead I got a "Do this and it'll work" type response. And it didn't. So what's the point in replying? I could reply to every question with what I THOUGHT might work but I only respond to what I definately know or have tried previously.
Otherwise you could be here all day sifting through the "Try this an see what happens..." type of responses.

Then I get SteveBausch reponding again with the extract from the Help files. I know how to proect the contents. I don't need this information. I didn't ask for it. I asked how to check if protection has been applied. This extract is explaining a method - trying to back-up what he said previously? I'm not interested in methods, I'm interested in properties. Again, a response that I think is hap-hazard, not thought through, not tested, simply extracted from the help file after searching for "Protect", and posted as a response.

This forum is a great help to myself, and I'm sure others, but it is worthless if people reply to posts just for the sake of replying. Perhaps they want to get an award for the most posts replied to. I know people get recognised for this, but is it recognition for correct responses that work, or just for the number of responses?

Again, I didn't mean to cause anybody any offense. I'm a very sarcarstic type of person and usually have my tongue firmly in my cheek but I suppose that doesn't come across too well in print.

Oh, and I would have awarded a star to GlenUK as he gave a response that was accurate, correct, and worked. However, I suspect SteveBausch got in there before me. I suppose I could have added a star too, as could the other responders, but then the reply would have too many stars and would appear to be a better response than what it is, afterall, it is only 1 line of code!