Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Workbook Code Queres
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", structu=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/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Workbook Code Queres
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", structu=True, Windows:=True End Sub -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Workbook Code Queres
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 "structu=" works even though it does not appear to be recognised by the sntax checker. Gleam "Bill Renaud" wrote: 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", structu=True, Windows:=True End Sub -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can protect worksheet then workbook but not Protect and Share in code | Excel Programming | |||
Protect Workbook Vs Protect Sheet | New Users to Excel | |||
Disable Tools, Protect, Protect Workbook | Excel Programming | |||
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ?? | Excel Programming | |||
Password Protect Workbook via code | Excel Programming |