Protection Password Prompt Problem

[tfraczak]

I would like the prompt the user to input a password if the password
string variable does not already have a value.

This is the code I currently have.

Private Sub Worksheet_Activation(ByVal Target As Excel.Range)
Dim PWORD As String
If PWORD Is Nothing Then
PWORD = InputBox("Enter protection password:", "Set
Protection Password")
End If
ActiveWorkbook.Protect Password:=PWORD, Structure:=True,
Windows:=True
ActiveSheet.Protect Password:=PWORD, DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayFormulaBar = False
End Sub

Perhaps I should move the prompt to Sheet1_Initialize()?

Another problem I am having is that the PWORD string variable isn't
being recognized by .Protect and .Unprotect.

I have tried:

ActiveWorkbook.Protect Password:=PWORD, Structure:=True, Windows:=True

and

ActiveWorkbook.Protect (PWORD, True, True)

They both do not work. Likewise for the ActiveWorkbook.Unprotect




I have only about a week's worth of self-VBA training. haha

Regards,
Tim

 

[Chip Pearson]

There are several problems in your code. First, there is no such event
as

Private Sub Worksheet_Activation(ByVal Target As Excel.Range)

There is a

Private Sub Worksheet_Activate()
event and a
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
event.

You can't just create events that you think might be useful. (Well,
you can create events, but that isn't relevant here.)

You have defined PWORD as a String variable, so you cannot use the "Is
Nothing" test on it. "Is Nothing" applies only to objects, not
fundamental data types. The code:

Dim PWORD As String
If PWORD Is Nothing Then

won't work and is meaningless.

Since you test PWORD immediately after declaring it:

Dim PWORD As String
If PWORD Is Nothing Then

or, syntactically correctly,

Dim PWORD As String
If PWORD = vbNullString Then

PWORD will always be empty since no code has executed that could have
possibly put a value in it.

Perhaps I should move the prompt to Sheet1_Initialize()?

Again, there is no such event. Sheet1 always exists, so there is no
point in the sequence of events that it would be initialized.

You best bet might be to use the Macro Recorder (Tools menu, Macros
item, Macro Recorder) to record protecting and unprotecting
worksheets. Macro recorder code is pretty poor and can't (or at least
shouldn't) be used in real code, but it does show you what objects and
which properties and methods of those objects are used to accomplish a
task.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)