Are you ready for Spreadsheet Day on October 17th?
Maybe you can add a Spreadsheet Day message to all your workbooks, using the technique described in this blog post.
It’s a macro that runs every time the worksheet changed. I’m sure your co-workers would enjoy that!
The Mission: Remind Users to Fill in Customs Form
In this example, the workbook has an order form, with a data validation drop down list, where you can select a customer name.
There are VLOOKUP formulas that pull the address information for the selected customer, to fill in the top of the Order Form sheet.
If the customer is located in Canada, you’d like to remind the user to fill in a customs form.
Create a Worksheet Change Macro
By using Event code in Excel VBA, you can make a macro run automatically if something happens on the worksheet.
In this example, you want the macro to run if there is a change on the worksheet.
The following code will make a message appear when the selected customer is in Canada (cell E7).
Private Sub Worksheet_Change(ByVal Target As Range) If Range("E7").Value = "Canada" Then MsgBox "Please fill in customs form" End If End Sub
Show Message Only When Specific Cell is Changed
On a worksheet where there are multiple cells that can be changed, you might want the message to appear only when a specific cell is changed.
In the order form, the message should:
- appear after the customer name is selected
- not appear if a product or quantity is entered
Revise the Worksheet Change Code
You can add a couple of lines of code, so it only runs when cell B5 is changed (the customer name).
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$5" Then If Range("E7").Value = "Canada" Then MsgBox "Please fill in customs form" End If End If End Sub
Message Box for Canada
Now the message box will only appear if cell B5 was changed, and the customer is located in Canada.
Watch the Excel Worksheet Event Macro Video
To see the steps for creating an Excel Worksheet Change Event macro, watch this short Excel video tutorial.
Download the Sample Workbook
To see the code, and experiment with the Order Form macro, you can download the Order Form Event Code workbook.
The file is in Excel xlsm (macro enabled) format, and is zipped. After you unzip and open the file, enable the macros, so you can see the message box.
________
I want to write a macro that allows me to copy a specific row to another worksheet in the workbook. For example there are dates in column A and if excel detects the date, i want it to copy the row that the date exists and paste it to the worksheet whose name is the date. How can you help me?
why i cannot see all the codes after i download the file…I should have seen the codes but what it appears was only the partial incomplete codes and the rest appears option explicit..
how should i view the codes to see how it was put up..
thanks