Worksheet Activate event if only one sheet in book
I don't think that the "unprotect part didn't work"..rather, the Activate
event didn't fire at all. If a worksheet is already the active sheet, even
if you explicity say Sheet1.Activate, the activate event would not fire.
Thus, when you added a second sheet, it worked because when the workbook
opens, Sheet1 is no longer active at the start. A workaround might be to
expose a Public Sub in your worksheet like this:
Public Sub ActivateMe()
Worksheet_Activate
End Sub
Then, in your workbook open, instead of calling the Activate method, do this:
ThisWorkbook.Worksheets("Sheet1").ActivateMe
"KD Trader" wrote:
Has anyone experienced this befo
I have the following code in the Activate event of Sheet1. There is
only one sheet in the workbook.
'
Private Sub Worksheet_Activate()
With ThisWorkbook.Worksheets("Sheet1")
.Unprotect Password:="abc"
.Range("A3").Value = "TestData"
.Protect Password:="abc"
End With
End Sub
'
I get an error message stating that cell A3 cannot be changed as it is
protected, which means that the "unprotect" part did not execute. The
Workbook Open event activates Sheet 1 (see the code further below).
If I execute the sub a second time (since opening the workbook) then
it works fine.
I have also noticed that the error goes away if I introduce a second
sheet and using the Workbook BeforeClose event to activate the second
sheet before the Open event activates Sheet1.
'
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Sheet1").Activate
ActiveSheet.Range("A12").Activate
End Sub
'
|