My VB Code doesn't work when workbook is protected/shared
Posted by Anita on February 08, 2002 6:09 AM
Can anyone help with this? I have the following code where I am unlocking and locking cells based on the value of another cell but I have to "protect & share" the workbook and I get an error message that says "Unprotect method of worksheet class failed" when I go to click in the cell. Any help would be appreciated!
Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
If Target.Column = 15 Or Target.Column = 17 Then
If Cells(Target.Row, 15) = "Promotion" Or _
Cells(Target.Row, 17) = "1" Then
ActiveSheet.Unprotect ("password")
Cells(Target.Row, 19).Locked = True
Cells(Target.Row, 19).Interior.ColorIndex = 34
Cells(Target.Row, 19).ClearContents
ActiveSheet.Protect ("password")
Else
ActiveSheet.Unprotect ("password")
Cells(Target.Row, 19).Locked = False
Cells(Target.Row, 19).Interior.ColorIndex = 0
ActiveSheet.Protect ("password")
End If
End If
End Sub
Posted by Mudface on February 08, 2002 6:16 AM
You would need to use the UnProtectSharing method first I think-
ActiveWorkbook.UnprotectSharing Password:="xxx"
Use ProtectForSharing to re-protect it.
Posted by Anita on February 08, 2002 6:32 AM
Thank you so much,I'll try this!
Posted by Mudface on February 08, 2002 6:38 AM
Anita, sorry, read this before you try the above
I'm really sorry, Anita, before you try it- I didn't realise your Sub was a Worksheet_Change one (should have read the message properly, doh), unprotecting a shared workbook means it will save itself, which means every time you change a cell in the areas you specified the workbook will unprotect, save itself, reprotect and save itself again. If it's a large workbook this could take quite some time and be very annoying....
Posted by Anita on February 08, 2002 1:24 PM
Re: Anita, sorry, read this before you try the above
I figured this out too - I tried your code and it gave me errors. I'm at a stand-still now and am not sure which route to go. I would still like to get it to work and the worksheet isn't that large but it would be opened by remote users so it might take a little more time, not sure. Can you think of any way that this might be able to be done that is more efficient?
Thanks again!
Anita
Posted by Anita on February 08, 2002 1:57 PM
Re: Anita, sorry, read this before you try the above
M -
I'm not at all proficient at VB code - could you tell me what sort of code I could put in my workbook and how to do it and I'll see if it affects the response time of the workbook?
Thank you again & again!
Anita I'm really sorry, Anita, before you try it- I didn't realise your Sub was a Worksheet_Change one (should have read the message properly, doh), unprotecting a shared workbook means it will save itself, which means every time you change a cell in the areas you specified the workbook will unprotect, save itself, reprotect and save itself again. If it's a large workbook this could take quite some time and be very annoying....
