Tom’s Tutorials For Excel: Adding a Cost Increase On Entry
With a WorksheetChange event you can enter a number in a cell, and do an immediate calculation upon entry of that number to display a more meaningful number.
Suppose you are entering prices of items to purchase, and you want to know the exact total cost of each item including the sales tax. With a tax rate of 8.5%, you can enter the known retail cost and have the actual out-of-pocket price be automatically calculated in the cell for you.
During entry of 2500 in cell B8.
Immediately after entry in cell B8, with the 8.5% tax rate added.
As seen in the following pictures, right-click your sheet tab, left-click to select View Code, and paste the below Change event procedure into the large white pane that is the worksheet module.
Press Alt+Q to return to your worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
'Open a With structure for the Target range object.
With Target
'Only evaluate range B5:B26 per your worksheet design.
'Also only aim to handle one cell changing at a time.
If Intersect(Target, Range("B5:B26")) Is Nothing _
Or .Cells.Count > 1 Then Exit Sub
'Do nothing if a target cell is cleared (Delete key pressed).
If IsEmpty(Target) = True Then Exit Sub
'Only accept numeric entries in the target range.
'If a non-number is attempted, disallow it and explain why
'with a Message Box.
If IsNumeric(.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", 48, "Numbers only!"
Exit Sub
End If
'Declate two Double type variables.
'One will handle the actual number entered.
'The other will define the tax rate as shown in cell B2.
Dim dblFactor#, NewVal#
dblFactor = Range("B2").Value
NewVal = .Value
'Re-enter the new calculated value.
'EnableEvents is set to False and back to True because
'you are changing a cell during a Change event.
Application.EnableEvents = False
.Value = (1 + dblFactor) * NewVal
Application.EnableEvents = True
'Close the With structure for the Target range object.
End With
End Sub
Tom
Nice tutorial and I can think of a number of uses for it, The best hints and helps I find are those that stimulate me to (a) think about what problems with can help me solve and (b) give me an opportunity to share some extension of what T read.
I did consider one alternative you and your other readers might consider. It takes two small changes.
The first was to name the cell with the tax rate, i.e. using the Excel Name Manager I name $B$2 “Tax_Rate” . This isn’t necessary for the second step, but I think it adds readability to what comes next.
Then I replaced the statement “.Value = (1 + dblFactor) * NewVal” with “.Formula = “= (1 + Tax_Rate ) *” & NewVal”.
Now, someone can change the tax rate, say shopping in a neighboring state or county has an advantage or tax rates change, and the whole sheet will recalculate. Also, by looking at the cell contents someone can still see the pre-tax price.