NY2DR
05-01-2005, 07:23 AM
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
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