The Starting PointTom Urtis, at Atlas Programming Management Inc., recently published a VBA hint for changing the list/sales price of an entry into a plus-tax amount.
The worksheet is quite simple. It has a title, a spot for the sales tax percentage, and an area for adding items and their prices.
The code is obviously professional and tested. It turns EnableEvents off and on at the right times, checks for errors in inputs, uses Intersect, etc. If nothing else, it's instructive, compact, (although plenty of comments to help the reader through), and more than a small step up from some of the examples I've seen elsewhere.
As I've said elsewhere, my favorite tips are those that make me think of the next step(s) I might take with what is offered.
First Step: Use Names
Second Step: .Formula instead of .Value
- On Worksheet_Change Event...
- Exit the sub if the new change is not in the expected range
- Clear the cell, display a message directing the user to enter a numeric value, then exit the sub if the value in the cell is not numeric.
- Once all error checking is complete, create and populate variables for the TaxRate and the original value
- Calculate the price-plus-taxes and put it into the cell