If a user wishes to run a Macro in Microsoft Excel 2007 when the D10 cell is found to be populated, worksheet change event must be used in the Excel Worksheet, not in the module. This article will show you how to run a macro when cell value changes.
For example, when you have a Macro that runs when the user clicks on a button, but instead you want the Macro to run when Cell D10 is populated.
In this case, you can keep your Macro in module one and you must use the worksheet change event in the worksheet itself, not a module.
So, whatever your original code is to run your Macro, we recommend you to put it back into its original form.
Since you are using Excel 2007, this is what you need to do:
- 1) Click on the Developer tab.
- 2) Click on the Visual Basic icon.
3) On the left pane window, double-click the sheet where you need your code to run.
4) Now, at the top of the code window you will see (General with a drop down, and (Declarations) with a drop down.
5) Click the drop down by (General) and select Worksheet.
6) Now in the code window you will see Private Sub Worksheet_SelectionChange(ByVal Target As Range) .
7) Remove the word "Selection". You want to remove it because that means when you click on a cell in the worksheet something will happen. You do not want that, you want to enter a value in D10. It should now read Private Sub Worksheet_Change<bold>(ByVal Target As Range).
- 8) <bold>Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$10" Then Call MyMacro End If End Sub
9) When you change the value in D10, the worksheet will "Call" your Macro.
Need more help with Excel? Check out our forum!
- Make a macro run when data entered in a cell [solved] > Forum - Excel
- Re: Make a macro run when data entered in a cell -multiple event [solved] > Forum - Excel
- Populate Multiple Sheets Based on Data Entered in a Main Excel Sheet
- Copy Data From One Excel Workbook to Another Using Macro
- Copy data from one excel sheet to another: automatically > Guide
- How to copy data to another workbook: using VBA
- Insert picture in Excel: cell, shortcut, using formula
- How to show or hide formula bar in Excel: VBA, shortcut
- How to enter multiple lines in a single Excel cell
- How to automatically transfer data between sheets in Excel
- VBA Excel color codes: index number, list
- Repeat rows in Excel: based on cell value, VBA
- How to use Excel color cell if formula
- What is the VBA code to select last sheet in workbook
- How to count names in Excel: formula, using COUNTIF
- How to use an IF statement to add 1 to total
- How to display multiple columns in a validation list
- How to insert file path in excel: cell, sheet
- Convert numbers to words in Excel: without VBA, formula
- How to copy a Macro into a blank cell
- How many IF statements can you nest in Excel
- How to apply a function to multiple sheets on Excel
- How to perform a partial cell match in Excel?
- How to change author name in Excel: spreadsheet, VBA
- How to insert GIF in Excel: 365, sheet, VBA
- How to take screenshot in Excel: shortcut, sheet, cell
- How to enable VBA in Excel: Mac, Office 365
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Fix arrows keys in Excel: Scroll lock, alternative method
- Insert a hyperlink in Excel: with text, to another tab