Hello Excellers.  Today let’s look at how to write an Excel Macro with some simple VBA to display a pop-up message box when a specific worksheet is activated.  This method can be useful if you need to remind users of your Excel workbook to carry our specific task.  For example, you may need to remind users to fill out a specific cell in the data, to send a copy of the workbook to a particular email address or some other task that is of importance.

What Does The Macro Do And How Does It Work?.

We use the Workbook_SheetActivate(ByVal Sh As Object) event.  This executes when a worksheet is selected.  We can use this when a user selects a specific worksheet to display a pop-up message box to deliver instructions.

Let’s take an example.  We want Excel to display a pop-up message box in excel to remind users to send the file to Finance once completed.  We want this event to occur when they select or activate Sheet5 in the Excel workbook.

Let’s Write The Macro!

First, let’s open up VBA window by hitting Alt+F11 or selecting the Developer Tab then Visual Basic in the Code group.  If you want more information on the VBA Window then check out my blog post below.

Macro Mondays – What are the Three Windows In The Visual Basic Editor For?

This code will sit in the Workbook Code window, as it is a workbook event which is triggered.

Step 1. Double click on ThisWorkbook in the VBA Project Window. Select Workbook and the SheetActivate Event.

Step 2. We use the On Error Resume Next code.  This simply instructs Excel to move to the next line of code if an error occurs.  I use it in this small piece of code, as it is simple enough to just ignore any errors that occur.  In some circumstances, you may need to have more information about the error,

Step 3 and Step 4. If the sheet name is Sheet5 that the user has activated then the pop-up message box will be displayed with the message we have set up in our VBA code.

Step 5. The code then ends.

Step 6. My favourite step.  Testing the Macro!.

Do You Want To Copy The Code?

[stextbox id=”grey”]

‘Macro by How To Excel At Excel’

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

If Sh.Name = “Sheet5” Then

MsgBox “Please Send To Finance When Completed”
End If

End Sub

[/stextbox]

If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

 

Next Steps?.  Do You Want To Learn How Write Excel Macros?  How About One Of Our Recommended Courses?.