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

 

[lenze]

I suppose that works!! I, however, would write it this way

Sub Unprotect_workbook()
Dim ws as WorkSheet
rspn = InputBox("Please enter password", "Password required")
If rspn <> "AS CSM" Then
    MsgBox "Incorrect password: workbook could not be unprotected"
Else:
For Each ws in Sheets
    ws.UnProtect rspn
Next ws
MsgBox "Workbook is unprotected"
End if
End Sub

lenze

 

[chesterbroek]

okay, i'll do as you stated!

is there a way i can close the message box upon clicking the upper right hand cross (i.e., close dialogue box) or upon clicking cancel...? unload me or so?



best,

Chester

 

[lenze]

Have you not tried that?? You really should try the solutions offered before asking irrelevant questions. If you had done so, you would see that the MsgBox will unload when "OK" is clicked!!
lenze

 

[chesterbroek]

sorry Lenze,

i don't know what solutions you're getting at... as said, i'm completely new to VBA and am trying to find my way around, so pardon my ignorance if this may seem so... i'm not trying to annoy or other, but am simply trying to get on with building my model.

so what is the best way for me to close the message box if i click the close dialogue box or cancel? would that be the unload function then?



cheers and my excuses!

Chester