Workbook_Open - ActiveSheet.Unprotect not working!
Posted by Michele on October 17, 2001 4:00 AM
I have a one-sheet workbook with this sheet password protected. Upon opening the workbook, in the "ThisWorkbook" module I have in "Private Sub Workbook_Open()" an unprotect, update a cell and then protect again. This works fine when I open the file/workbook directly. However, if I open it via another Excel-workbook/Excel-file it crashes on my udate of the cell saying the cell is protected. I have checked and confirmed that under this indirect open, the unprotect doesn't take place.
Can anyone help? What's wrong with my code? Or what's wrong with Excel/VB
Following is a copy of my code:
Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
ActiveSheet.Protect ("moqrpsw"), DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.CommandBars(1).Enabled = False
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
Range("HypDate").Select
ActiveWorkSheet.Unprotect ("moqrpsw")
Range("CoName").Value = Left(ActiveWorkbook.Name, 3)
ActiveSheet.Protect ("moqrpsw"), DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("HypDate").Select
Beep
End Sub
Many thanks
Michele
Posted by Richie Turner on October 17, 2001 5:52 AM
Hi Michele
Not sure if this will solve it but it's a suggestion..
I've noticed you've used Active.Worksheet Protect. Could it be that when this sheet is opened via another this isn't the active sheet. Could you try replacing ActiveSheet with the actual sheet name or using the Activate method on the worksheet/book to make sure the offending sheet is active.
Like I say, not sure if this helps but I'd be interested to hear if and how you solve it.
cheers
Richie
Posted by Michele on October 17, 2001 8:11 AM
Hello Richie,
Many thanks for your help.
You are correct that the ofending sheet is not active during the WorkBook_Open operation. So I tried to make it active by doing 'Worksheets("Sheet1").Activate' but that didn't work either.
Can you give me details on how to do the Activate Method you mentioned so I can try it or I welcome any further ideas. I've tried putting my code in the Sheet1 module in Private Sub Worksheet_Activate but that doesn't work unless I have another sheet and switch from this other sheet to sheet1.
Thanks again.
Michele
