OnOpenExcelWorksheet Event To Trigger When Worksheet Opens?

Question

How can I run code when a Worksheet opens? (The code that I want to run is contained in the opening worksheet)

Answer 1

I'm not sure how to get code to run when a worksheet opens, but you can get it to run when a workbook opens.

In the VBA editor, open the Microsoft Excel Object called "ThisWorkbook." At the top of the editor window, you should see two drop-down boxes: (General) and (Declarations). Change the (General) combo box to be "Workbook."

This will give you method called Workbook_Open(). Code placed in this method will execute when you open the Excel Workbook.

---

Furthermore, you have more events at your disposable, available in the (Declarations) section when you have Workbook selected, such as SheetActivate and SheetChanged, among others. I haven't used those methods, but they may be something to try if you need events related to individual worksheets and not just the entire workbook.

Answer 2

You can put certain code in the Worksheet_Activate() function which will run when the sheet is selected. Additionally, use the Worksheet_Deactivate() to run code when you leave the wokrsheet and go to another one. These functions go in the worksheet object code.

Private Sub Worksheet_Activate()
    MsgBox ("Hi")
End Sub

Private Sub Worksheet_Deactivate()
    MsgBox ("Bye")
End Sub