How to get worksheet_activate code to run when a workbook is opened

[Mike Clemens]

I have code in the worksheet_activate event for each of the worksheets in a
workbook. When the workbook is opened the worksheet_activate event for the
worksheet that was active at the last save is not fired.

I tried to get around this by placing "Worksheets(PROJECTS_WKS).Activate" in the
workbook_open event. The PROJECTS_WKS constant holds the name of the first
worksheet in the workbook. This only works if PROJECTS_WKS wasn't the active
worksheet at the time of the last save.

Is there anyway at workbook open time to get the worksheet_activate event to
fire for the worksheet that was active when the last save was done?

I know I could turn off screen updating and then activate some other worksheet
in the workbook and then switch back to one that was active to get the event to
fire.

That would work unless the workbook only contains one worksheet. I suppose then
I could just but the code that is in worksheet_activate into the workbook_open
event.

Is there some solution that would work in both cases.

Thanks

Mike Clemens

 

[Mike Clemens]

I have code in the worksheet_activate event for each of the worksheets
in a workbook. When the workbook is opened the worksheet_activate event
for the worksheet that was active at the last save is not fired.

I tried to get around this by placing
"Worksheets(PROJECTS_WKS).Activate" in the workbook_open event. The
PROJECTS_WKS constant holds the name of the first worksheet in the
workbook. This only works if PROJECTS_WKS wasn't the active worksheet at
the time of the last save.

Is there anyway at workbook open time to get the worksheet_activate
event to fire for the worksheet that was active when the last save was
done?

I know I could turn off screen updating and then activate some other
worksheet in the workbook and then switch back to one that was active to
get the event to fire.

That would work unless the workbook only contains one worksheet. I
suppose then I could just but the code that is in worksheet_activate
into the workbook_open event.

Is there some solution that would work in both cases.

Thanks

Mike Clemens

Shortly after I sent the post to the newsgroup I came up with a solution. I
changed the all of the worksheet_activate procedures from private to public and
added the following code at the end of the workbook_open event procedure:

Select Case ActiveSheet.Name
Case PROJECTS_WKS: Call WKS_Projects.Worksheet_Activate
Case PID_WKS: Call WKS_PID.Worksheet_Activate
Case SERVICEITEM_WKS: Call WKS_ServiceItem.Worksheet_Activate
Case CLASS_WKS: Call WKS_Class.Worksheet_Activate
Case USERS_WKS: Call WKS_Users.Worksheet_Activate
End Select