protection and macros/code

scroce

New Member
Joined
Aug 20, 2002
Messages
15
If you go to Tools>Protection>ProtectSheet> and then Enter a password and DESELECT all options, then you get a nice read only sheet that allows other users to see what you've done, but they can't copy and paste it.

PROBLEM - How do you replicate that programatically in VBA? I'm trying to do something like:


Public Sub Workbook_BeforeClose(Cancel As Boolean)

If "the sheet is already protected" then
"Just exit the workbook."
Else
"Protect the sheet as described above."
End If

End Sub

I got it to turn on the protection with a password, but no matter what I do, I can't replicate with code the deselection of those options - in other words, it allows the user to hilite and copy/cut ranges.

There has to be a way to do this with code. Help!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You can try with these properties:

?Sheet1.ProtectContents=True
False
?Sheet1.ProtectionMode=True
False
?Sheet1.ProtectDrawingObjects=True
False

the common is to use the first and the third to test for any sheet protection.
 
Upvote 0
If I understood you correctly, I tried the procedure below:

Public Sub Workbook_BeforeClose(Cancel As Boolean)

Sheet1.ProtectContents = True
Sheet1.ProtectionMode = True
Sheet1.ProtectDrawingObjects = True

End Sub

....and it didn't compile. it said "Can't assign to read only property"

I'm using excel 2002 - maybe this code doesn't work in this version?
 
Upvote 0
I just went to Tools|Macro|Record New Macro and did your protection selection stuff. This is what it gave me for the code:

<PRE>
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
</PRE>

Seems to work for me (I'm using 2002 as well).

Hope it helps,
 
Upvote 0
KUDOS TO YOU - Von - you take the prize in round 1 -I can't understand why I used the macro recorder repeatedly and NEVER ONCE did I get anything showing .EnableSelection = xlNoSelection method/property.

It must be the random perversity of inanimate objects.

OK - Round 2. Here is the successful code:

Private Sub Workbook_Open()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection

End Sub

Why doesn't it work on the:
Public Sub Workbook_BeforeClose(Cancel As Boolean) event?

i.e. - a user could still beat my protection if they closed the file without protecting it and then opened it up with the macros disabled. I want to force the worksheet protection upon closure of the workbook even if the user forgets to put on the protection.

Excel doesn't seem to recognize this code on BeforeClose. I even tried Workbook.Save after the code, but it didn't work.
 
Upvote 0

Forum statistics

Threads
1,226,588
Messages
6,191,891
Members
453,684
Latest member
Gretchenhines

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