Protecting/Unprotecting Workbook

  • Thread starter Thread starter Sonny Maou
  • Start date Start date
S

Sonny Maou

I'd like to be able to protect a workbook from changes via VBA. In Word,
I have these two bits of code...

Sub LockItUp(doit)
If doit <> wdNoProtection Then
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=doit, NoReset:=True,
Password:="myPass1234"
End If
End If
End Sub

Function UnlockIt()
Dim wasLocked
wasLocked = ActiveDocument.ProtectionType
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect "myPass1234"
End If
UnlockIt = wasLocked
End Function

The function UnLockIt() simply locks up a document with a password and
returns what the prior protection scheme was. LockItUp() simply locks it
back up with whatever parameter it is given. I usually run these during
save, close, open, etc...

I generally know now how to do it in Excel, but how do I do it most
effectively? :)

Thanks!
 
Sub LockIT()
If activeworkbook.protectcontents = False then activeworkbook.protect "mypass"
end Sub

Sub UnlockIT()
if activeworkbook.protectcontents = true then activeworkbook.unprotect "mypass"
End Sub
 
Steven said:
Sub LockIT()
If activeworkbook.protectcontents = False then activeworkbook.protect "mypass"
end Sub

Sub UnlockIT()
if activeworkbook.protectcontents = true then activeworkbook.unprotect "mypass"
End Sub
Click to expand...

Thanks, Steven.

When I go to "unprotect" the workbook, will it respect the "locked"
cells I have on the worksheet?
 
I'm not sure what you mean by respect.

Cells will only be locked if the worksheet is locked:
activesheet.protect "mypass"

if a worksheet is not locked then cells will not be locked even if their properties say they are.

sub LockSheet
if activesheet.protectcontents = false then activesheet.protect "mypass"
end sub

sub UnLockSheet
if activesheet.protectcontents = true then activesheet.unprotect "mypass"
end sub

sub LockWorkbook
if activeworkbook.protectstructure = false then activeworkbook.protect "mypass"
end sub

sub UnLockWorkbook
if activeworkbook.protectstructure = True then activeworkbook.unprotect "mypass"
end sub
 
Steven said:
I'm not sure what you mean by respect.

Cells will only be locked if the worksheet is locked:
activesheet.protect "mypass"

if a worksheet is not locked then cells will not be locked even if their properties say they are.
Click to expand...

Let me explain what I'm trying to do more clearly: I want to toggle
between two worksheet states...

1. all cells locked from use (total protection)
2. some cells locked from use (partial protection)

I'm thinking I'll have to somehow hardcode the locked cells references,
but I don't want to have to do that! :)
 
Back
Top