Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ' |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ' |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Activate event if only one sheet in book
Thks, I'll give it a try.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet activate method in beforesave event | Excel Programming | |||
search all sheets in book to find value and activate sheet with va | Excel Programming | |||
Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
Worksheet Activate Event | Excel Programming | |||
Activate sheet event | Excel Programming |