 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
Auto Run Excel Macros Upon Data Entry.
Excel has the ability to automatically run/execute any Excel macro code of our choosing when data in any cell on a Worksheet changes. We can do this via the Worksheet_Change event. We can even specify which cell/cells must be changed before running our code.
Worksheet_Change Event
In Excel 97 Microsoft introduced what are called Events , one of these is the Worksheet_Change Event. The Worksheet_Change event is a procedure of the Worksheet Object and as such, the Worksheet_Change procedure MUST reside in the private module of the Worksheet Object.
Accessing the Private Module of the Worksheet Object
There are at least 2 ways to gain access to the private module of the Worksheet Object.
1) While in Excel proper, right click on the Worksheet name tab and choose View Code.
2) While in the VBE double click the Module called Sheet* (Sheet*), seen in the Project Explorer (View>Project Explorer (Ctrl+R)) under Microsoft Excel Objects.
Once here, you can select "Worksheet" from the Object drop down list, located in the top left of the module pane. After you have selected "Worksheet" Excel will default to;
Private Sub Worksheet_Change(ByVal Target As Range)
Note, in Excel 97 accessing any the Module called Sheet* (Sheet*) would automatically add the Workbook_Change event.
How to use the Workbook_Change Event
You will note in the: Private Sub Worksheet_Change(ByVal Target As Range) there is a predefined variable called Target. This represents the Range Object of the cell that was changed after pushing Enter. It is not the cell you land in after pushing Enter! It is the variable called Target that we can use to determine exactly which cell has changed. See the simple example below
Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "You just changed " & Target.AddressEnd Sub
Note how we use the Address Property of the Range Object (Target) to identify the cell.
Let's now see an example of how we can use the Address Property of the Range Object (Target) to only run code when a specific cell is changed. In the case below, we are going to multiply the new entry in A1 by 2, but only if a number has been entered. We have also told Excel to do nothing (exit sub) if more than one cell has been changed, or the data in A1 has been deleted.
Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$A$1" Then 'Ensure target is a number before multiplying by 2 If IsNumeric(Target) Then 'Stop any possible runtime errors and halting code On Error Resume Next 'Turn off ALL events so the Target * 2 does not _ put the code into a loop. Application.EnableEvents = False Target = Target * 2 'Turn events back on Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub
In the above code we have made use of Application.EnableEvents to ensure the multiplying of A1 by 2 does NOT cause the change event to run again and get itself in a loop.
If want the change event to run when/if more than one cell is changed, we would remove the Target.Cells.Count > 1 and replace all occurrences of Target (but not ByVal Target as Range) to Target(1,1). This then forces Excel to only consider the active cell should more than one cell be changed. If you are not aware, there can only ever be 1 active cell in any selection and that is always the cell you Start your selection from.
Monitor More Than 1 Cell (Target)
It is often needed that we run code when any cell in a range of cells changes. For example, lets say each time any cell in the range A1:A10 changes we need to multiple by 2. In this case we would use an If Not Statement, with the Intersect Method to determine if the Target is within the range A1:A10. That is;
If Not Intersect(Target, Range("A1:A10")) Is Nothing ThenIf our range of cells to monitor is a non-contiguous range we would use;
If Not Intersect(Target, Range("A1,C1,E1")) Is Nothing ThenRunning Code in Another ProcedureIf you already have a Procedure that resides in a standard public module (Insert>Module) we would use the Run Statement.
Run "MyMacro"
When/if doing as shown above, you will NOT be able to reference the variable Target from within MyMacro. If you need to you will need to declare a Global Variable at the top of any standard module, preferably the one that houses MyMacro, then Set Target to it. That is;
Public rTarget as Range
Then, in the Change Event we would use;
Set rTarget = Target
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates