Protect ThisWorkbook in xl97

[dipitiduda2]

Hello,

I'm trying to use the ThisWorkbook.Open() event in an xl97 workbook (code originally written in xl2002) with multiple worksheets to .Protect/.Unprotect the macros running within each worksheet.

The macros run correctly on the first fire, but upon the creation (using ActiveSheet.CopyAfter:=ActiveSheet) of another worksheet in the workbook, the macros are disabled in the new worksheets. The number of worksheets to be copied into the workbook is unknown.

I also had to change the syntax of the ThisWorkbook_Open() macro event from xl2002 to xl97 to allow for one of the arguments set to = False. See code below.

Problem: In xl97 I cannot find the correct syntax that will allow me to .Protect/.Unprotect "For Each ws In ThisWorkbook.Worksheets.Protect....

Here's my code for the open event:

Private Sub Workbook_Open()
    'Tasks to be completed each time the file is opened:
          
    Sheets("Instructions").Select
    Begin_Form.Show
    
    On Error Resume Next
    
    For Each Sh In ThisWorkbook.Worksheets
            Password:="texas", _
            DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=False, _
            UserInterfaceOnly:=True
            Next
    End Sub

I know I'm missing the .Protect/.Unprotect code, but cannot figure out how to say, "apply these arguments to each worksheet in this workbook whether named or not" in this event.

Any assistance is greatly appreciated. I have looked on every web site that I can find for the answer, but to no avail.

 

[Zathras]

Here's one way:
[blue]

Private Sub Workbook_Open()
Dim sh As Worksheet

[green]

  'Tasks to be completed each time the file is opened:

[/color][/code]
Sheets("Instructions&quot.Select
Begin_Form.Show

For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Protect Password:="texas", _
DrawingObjects:=True, Contents:=True, _
Scenarios:=False, UserInterfaceOnly:=True
Next sh
End Sub
[/code][/color]

 

[dipitiduda2]

Zathras,

Thank you for responding so quickly. I tried this, and although the coding works OK, I think my problem lies in the macro that actually "adds" the worksheets to the file.

I will try other fixes since this did not solve the macro deactivation problem and keep ya' posted when I find the fix.

 

[dipitiduda2]

As today is Monday, I have a fresh pair of eyes on this coding snafu.....Tried your coding fix, thought it worked on Friday, and am now getting a MsgBox "Compile Error - Expected Expression" on the "DrawingObjects:=True" line of code.

Also, have not resolved the macro deactivation problem yet which was the point of this additional coding in the ThisWorkbook_Open() event.

Any other suggestions? Thank you!

 

[Zathras]

I have no idea what is causing the error... Also I'm not sure exactly what you are trying to accomplish. But you might consider structuring your code a little differently: Put this in the workbook events code page:
[blue]

Private Sub Workbook_Open()
Dim sh As Worksheet
  'Tasks to be completed each time the file is opened:
  Sheets("Instructions").Select
  Begin_Form.Show
  Call ProtectSheets
End Sub

[/color]
And put this in a separate code module:
[blue]

Sub ProtectSheets()
Dim sh As Worksheet
  For Each sh In ThisWorkbook.Worksheets
    On Error Resume Next
    sh.Protect Password:="texas", _
         DrawingObjects:=True, Contents:=True, _
         Scenarios:=False, UserInterfaceOnly:=True
  Next sh
End Sub

[/color]
Then in the routine where you are adding a new sheet, include this line of code:
[blue]

  Call ProtectSheets

[/color]
to re-protect all sheets (including the new one).

Or simply process the NewSheet event from the workbook code module like this:
[blue]

Private Sub Workbook_NewSheet(ByVal Sh As Object)
  Call ProtectSheets
End Sub

[/color]
That will protect the sheets whether you add one by code or the user adds one manually. (If you don't want to re-protect all sheets when one is added, tweak the code accordingly.)