Worksheet Activate event if only one sheet in book
 

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
'

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
'

Worksheet Activate event if only one sheet in book
 

Thks, I'll give it a try.

Worksheet Activate event if only one sheet in book
 

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


On Mar 13, 7:42 am, "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
'