Write An Excel Macro To Display A Message When Data Has Been Updated- Macro Monday

Hello Excellers. Welcome back to another #macromonday blog post. I want to show you how to write an Excel Macro which will display a message when data has been updated. So how will this be useful?. Well, I have a user that waits for me to update a worksheet. By using this small piece of VBA code I can display a message to them when they open the workbook. This message will tell them if the workbook is ready for them to use, and they can go ahead and distribute the report figures to their team.

What a great and automated way to communicate updates to the user. The user does not need to ask if the file is updated or is completed. The user simply opens the file. If the user sees the pop-up message, they know they have the updated file. This takes a bit of preparation to inform the user that this is the way the final file will be ready for them to use.

This really is a really easy but of Excel VBA code. Don’t forget to copy the code at the bottom of the blog post. You can adjust it for your own use quickly and easily. Just amend the range of cells value and the message that you want the pop-up box to say to the user. Tailor it to your Excel solution.

If you want to check out my other blog posts on Message and Pop Up boxes then check out the links below.

A VBA Macro To Display A Pop Up Message When A Specific Sheet Is Activated – Macro Mondays

Macro Mondays – How To Use A Message Box To Allow The User To Confirm They Want To Run A Macro.

Macro Mondays – A Macro To Create An Alert When A Cell Value Exceeds Its Target Value

What Does The Macro Do?

The Macro is triggered by the Workbook_Change event. Once the cell or cells you specify have changed to a value you have set in the VBA code a pop-up box is displayed to the user.

How Does The Macro Work?

Do not store the code in a Standard Module. See below how to get this code into the Excel private module of the Excel Worksheet Object.

Here Are The Coding Steps. Follow Them. They Are Easy.

Step 1. Open Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. Once you have opened the Visual Basic Editor, from the left pane click on the worksheet you want this code to reside and change (General) to Worksheet and (Declarations) to SelectionChange. The Visual Basic editor should like the screenshot below.

Step 2. The range needs to be set at of D2 to be compared to a value specified in the code.

Step 3. The previous line of code triggers the Message Box or pop up box. This is only if the condition that has been specified is met. The message we have coded into the procedure is displayed. In this example it is “This Report Is Completed – Please Delete Cell D2 Before Issuing” that is displayed.

Step 4. The code ends at this stage.

Step 5. Test Your Code! This is my favourite part of the process.

What Next?. Want To Copy The Code? Go On You know You Want To.

[stextbox id=”grey”]

Option Explicit
‘Macro by How To Excel At Excel
‘1. Use the Worksheet_Change Event
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘2. Next, specify the cell range, and it is compared to the value we specify
If Range(“D2”).Value = (“10”) Then

‘3. A message box is displayed
MsgBox “This Report Is Completed – Please Delete Cell D2 Before Issuing”

End If

‘4. The Code ends
End Sub

[/stextbox]

Want To Learn Even More About Macros? Go On You Know You Want To.

That’s it. How handy is that? Have you used this method of notifying users?. Do you use another way of flagging information to Excel users?. Why not share in the comment box below. I would love to hear your Excel VBA stories to share with other Excellers on this blog.

You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.

If you want more 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.

How To Excel At Excel