Worksheet Activate event if only one sheet in book

[KD Trader]

Has anyone experienced this before:

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
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

[Vergel Adriano]

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]

Thks, I'll give it a try.

 

[Carl Hartness]

You could just put your code inside the open.
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Sheet1")
.Unprotect Password:="abc"
.Range("A3").Value = "TestData"
.Protect Password:="abc"
End With
End Sub
Also, Private Sub Workbook_Activate() always fires after
Workbook_Open(), so you could put it there, and it would work every
time you switch to this workbook from another.

Carl