how to set the active sheet using Excel PIA

Question

Hi,

I'm using multiple sheets within my Excel application and want to activate (set focus on) a particular sheet upon exit.. Basically I want to set ActiveSheet property on my workbook, but since the property is readonly, is there any other way to achieve this?

Thanks,

Sam

Answer 1

In the VBA Editor within Excel (Alt+F11), go to the ThisWorkbook section of the project. From the left pulldown, select "Workbook". On the right pulldown, select "BeforeClose".

Any code you put in here will occur after the user opts to exit the sheet, but before it's actually closed. In this space, enter the following code, substituting the name of your sheet:

Worksheets("Sheet1").Activate

Your final sub should look like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Activate
End Sub