Using VBA: protecting and unprotecting a workbook

[chesterbroek]

hi all,

i'm relatively new to VBA code and would like to protect my workbook when the workbook is opened.

subsequently, i would like the user to be able to unprotect the workbook via clicking a button. this button would first ask the user for a password before unprotecting the workbook.

how can i code this through VBA?

i already built the following VBA code:

upon opening the workbook:

Private Sub Workbook_open()
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
ActiveWindow.DisplayWorkbookTabs = False
Sheets("1. Cover").Activate
Disclaimer.Show
ActiveWorkbook.Protect password:="AS CSM"
Application.ScreenUpdating = True
End Sub

upon pressing the button:

Sub Unprotect_workbook()
Dim psswrd As String
psswrd = InputBox("Please enter password", "Password required")
If psswrd = "" Then Exit Sub
On Error GoTo Error
ActiveWorkbook.Unprotect password:=psswrd
MsgBox "Workbook is unprotected"
Exit Sub
Error:
MsgBox "Incorrect password: workbook could not be unprotected"
Exit Sub
End Sub

this VBA code doesn't seem to function properly though... any help is more than welcome!!!



thanks!

Chester

 

[chesterbroek]

thanks Lenze!

but i'm still confused:

1.) is there no code to protect all workbook sheets in one go?
2.) what exactly distinguishes the workbook protect from the worksheet protect code?
3.) how can i best build this unprotect button? i.e., with the code you just wrote?



thanks a bunch!

Chester

 

[chesterbroek]

does this look alright:

Private Sub Workbook_open()
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
ActiveWindow.DisplayWorkbookTabs = False
Sheets("1. Cover").Activate
Disclaimer.Show
ActiveWorkbook.Protect "AS CSM"
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect "AS CSM", UserInterFaceOnly:=True
Next Worksheet
Application.ScreenUpdating = True
End Sub

or can we slim this down even more? moreover, why would one require the activeworkbook.protect code?



cheers,

Chester

 

[lenze]

I'm confused as to what you want!!

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets
ws.Protect "password", UserInterFaceOnly:=True
Next ws
End Sub

This will protect each sheet in the WorkBook when it is opened!!
Why do you need to protect the WorkBook
I see No use for any of these lines in your code

Application.ScreenUpdating = False
Application.DisplayFullScreen = True
ActiveWindow.DisplayWorkbookTabs = False
Sheets("1. Cover").Activate
Disclaimer.Show
ActiveWorkbook.Protect "AS CSM"

lenze

 

[chesterbroek]

aha!

Lenze, the other lines refer to other code that is run when the workbook is opened; they should be there to help my model! don't worry about those though... (they work fine). i'm just interested in getting this protect / unprotect code going!

should i use the workbookprotect code at all? don't really know if it's useful...!



cheers,

Chester

 

[lenze]

should i use the workbookprotect code at all? don't really know if it's useful...!

I have no idea!! Do you need to protect the WorkBook, or just the sheets?? Your call!!
lenze

 

[chesterbroek]

Lenze,

as i mentioned, i would like to protect the entire workbook, i.e., all the sheets contained in the workbook. more to the point, the user should only be able to enter data in some cells i leave unlocked (unprotected) for modeling purposes. is this clear?



sorry and best,

Chester

 

[lenze]

Repeat earlier comment. You lock or prevent entry in cells by protecting the "WORKSHEET". Protecting the WorkBook has no bearing on data entry. Protecting the WorkBook protects the "Stucture" of the file!
lenze

 

[chesterbroek]

right, so in this case i should use the protect worksheet code as you indicated:

Private Sub Workbook_open()
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
ActiveWindow.DisplayWorkbookTabs = False
Sheets("1. Cover").Activate
Disclaimer.Show
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect "AS CSM", UserInterFaceOnly:=True
Next Worksheet
Application.ScreenUpdating = True
End Sub

this is what i currently have!

however, will this allow me to define cells that the user can still alter?



best,

Chester

 

[lenze]

Any cell that is "UnLocked" (Cells>Format>Protection Tab) will still be able to be edited. Cells that are "Locked" will not be able to be changed!!

lenze

 

[chesterbroek]

gotcha!

and now, to build the unprotect button, does this code suffice:

Sub Unprotect_workbook()
input = InputBox("Please enter password", "Password required")
If input = "AS CSM" Then
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.unProtect input
Next Worksheet
MsgBox "Workbook is unprotected"
Else: MsgBox "Incorrect password: workbook could not be unprotected"
End if
End Sub


thanks for bearing with me!

Chester