Protect Workbook Code Queres

[Gleam]

Private Sub Prot()
' Purpose of this macro is to show a possible problem with Excel 2003
' (Visual Basic 6.3 Version 9972)

' Select another workbook and run this macro twice. First time OK. Second
' time protection is removed from workbook.
For i1 = 1 To ActiveWorkbook.Sheets.Count
Worksheets(i1).Protect ("PWord7")
Next i1
' Next lines will remove protection second time macro is run - but should not
On Error Resume Next
ActiveWorkbook.Protect ("PWord7")
ie = Err.Number ' 1004=already protected?
On Error GoTo 0
End Sub
Private Sub Prot2()
' Purpose of this macro is to show 2 possible problems with Excel 2003
' (Visual Basic 6.3 Version 9972)
' Next line is rejected by Auto Syntax Check
activeworkbook.Protect ("PWord3",True,True)
' structure is not picked up as a key word in next line
ActiveWorkbook.Protect Password:="PWord4", structure:=True, Windows:=True
End Sub




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...25df458&dg=microsoft.public.excel.programming

 

[Bill Renaud]

I run Excel 2000, so can't verify that these are problems in Excel 2003,
but I have a hunch that this is a programming problem, not a problem with
Excel. You should be using object variables in your code, so that you can
see the values change in the Locals window when you are stepping through
code.

In Protect1, you left the parameters out for the Structure and Windows
arguments in the Protect method. The default values are FALSE for these, so
when you execute the Protect method, you effectively UNProtect the
workbook! You should include the parameters (similar to that in Protect2)
to be sure that the workbook is set the way you want it. I modified this
routine to check for whether either of these arguments are alreay true. If
so, then protecting the workbook again is skipped.

In Protect2, you had parentheses around the arguments
(activeworkbook.Protect ("PWord3",True,True)). In VBA, parentheses are not
included when you do not set the return value to a variable. Excel 2000
recognizes the structure argument fine, even though it is in lower-case.

Your statement syntax should be like the examples immediately below:

Object.Method arg1, arg2 'No parentheses.
Variable = Object.Method(arg1,arg2) 'Parentheses included to assign
result to a variable.

'----------------------------------------------------------------------
Private Sub Protect1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
ws.Protect "PWord7" 'Should include parameters here for safety.
Next ws

With wb
If .ProtectStructure Or .ProtectWindows _
Then
'Do nothing. Either structure or windows are already protected.
Else
'Default value for Structure and Windows is FALSE.
'This effectively UNPROTECTS the workbook!
.Protect "PWord7" 'Should include parameters here for safety.
End If
End With
End Sub

Private Sub Protect2()
Dim wb As Workbook

Set wb = ActiveWorkbook

wb.Protect Password:="PWord4", structure:=True, Windows:=True
End Sub

 

[Gleam]

Bill
Thank you for these comments.
I was alarmed because I was expecting protection to be applied and found
that it had gone.
I agree thqat the "structure:=" works even though it does not appear to be
recognised by the sntax checker.

Gleam