Shared Workbook - How can macro work?

[NY2DR]

Hello,

I have a workbook that is shared and created a macro to protect some field. However, I get "Debug" errors on it. When I remove it from being shared, it works well.

What can I change to correct this?:think:


Private Sub Workbook_Open()
Dim Sh As Worksheet
' Protect/Unprotect sheets dependent on who's logged in
Select Case Environ("username")
Case "computer_login"
For Each Sh In Worksheets
Sh.Unprotect
Next Sh
Case Else
For Each Sh In Worksheets
Sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next Sh
End Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sh As Worksheet
' Protect all sheets before closing
For Each Sh In Worksheets
Sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next Sh
End Sub

To give you an idea what I want to do;

1- The workbook sheets are all protected before before being shared, with the exception of columns H & I on all of them. These field are to remame unprotected so that users may input their data. The rest of the field will be closed to them.

2- If it is the login users name, then ALL sheets are to be unprotected for him and then when closed, protect all sheets agian.

Please: pray2: tell me this can be done in a shared workbook and if so, how.

Thank you

[geekgirlau]

Ahh, you've discovered the joys of working with a shared workbook :banghead:

When you look at the help in Excel, you'll discover a disturbingly large list of "features that are unavailable in shared workbooks" (otherwise entitled "get ready to completely rewrite your code because half of it will no longer work").

One of the features that you can no longer use is "Protect or unprotect worksheets or the workbook".

[NY2DR]

Oh man!!!

I was hoping there was some "undocumented" feature around this.
If there is any hope it will be greatly appreciated.

Thank you geekgirlau:)