by Allen Wyatt
(last updated October 7, 2016)
Julie would like to have some input cells on a worksheet that, when someone enters a value, automatically add 20% to whatever was entered. For instance, if someone enters 200 into one of these cells, what is actually entered is 240.
There are a multitude of ways you can approach this task using macros. The best approach is to create a macro that is run, automatically, when a cell is changed in the worksheet. You can then check to see if the change was made in one of the input cells and adjust values accordingly. The following example modifies the entered value if it was made in any of three cells: A1, C3, or B8.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rInput As Range Dim rInt As Range Dim rCell As Range 'change the input cell range as desired Set rInput = Range("A1, C3, B8") Set rInt = Intersect(Target, rInput) If Not rInt Is Nothing Then For Each rCell In rInt If IsNumeric(rCell) Then With Application .EnableEvents = False rCell = rCell * 1.2 .EnableEvents = True End With End If Next End If End Sub
Remember that this is an event handler, which means it is triggered (in this case) whenever something changes in the worksheet. In order to use this macro, right-click on the worksheet tab and choose View Code from the resulting Context menu. Excel displays the VB Editor and you can then add the Worksheet_Change code.
Note that the key to figuring out whether the change was done in one of the three defined input cells is the Intersect function. It checks to see if there is an intersection between the Target range (the changed cells that triggered the Worksheet_Change handler) and the rInput range (your input cells). If there is, then rInt would contain the cells that did intersect.
The macro then steps through those cells and, if the cells contain numeric values, it multiplies those cells by 120%. (Multiplying by 120% is the same as increasing the value by 20%.) Note that the .EnableEvents property is set to False when the multiplication is done; if this safeguard were not taken, then each multiplication would trigger this event handler again and you would repeatedly (and forever) multiply the cell value by 120%.
If you wanted to actually do some other processing to the values—such as rounding to a specific number of decimal points or to an integer value—then you simply need to make a change to the single line that actually does the multiplication.
If your input cells are in a contiguous area, a better approach might be to define those input cells as a named range, and then use that named range within the macro to determine the intersection of the changed cells. In that way you don't need to modify the macro when or if your group of input cells change.
To use this approach, let's say that your range of input cells is B7:B19. Select those cells and then, using the name box at the upper-left corner of the cells area, enter the name "plus20pct". This action assigns the name to the range. You can then use that name within the macro.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rInt As Range Dim rCell As Range 'change the input cell range as desired Set rInt = Intersect(Target, Range("plus20pct")) If Not rInt Is Nothing Then For Each rCell In rInt If IsNumeric(rCell) Then With Application .EnableEvents = False rCell = rCell * 1.2 .EnableEvents = True End With End If Next End If End Sub
Note that the only change is in the way that the intersection of cells is determined—the Intersect function uses the "plus20pct" range as a parameter. Everything else works as before.
Now that you've seen how to do this using macros, the question remains as to whether you really should do it using macros. First, there are limits to what these macros can do. For instance, what if your user enters a date or time into one of the input cells? Internally, Excel handles dates and times as numbers, meaning that they, too, would be increased by 20%.
Second, you need to consider what happens to your worksheet if someone modifies the worksheet structure by adding or delting rows or columns. The macros are using either absolute cell references (A1, C3, and B8) or a named range (plus20pct). While the named range may be adjusted by row or column addition or deletion, the absolute cells references would not change. Thus, you could end up with the macro checking (and adjusting) cells that are no longer the expected data entry cells.
Third, let's say that someone enters a value (200) into one of your input cells. It is automatically increased by 20% and becomes 240. The person sees this change and wondered what happened, so they select the cell and press F2 to start to edit the cell. Before they make the change, they remember that "Oh, yeah; it is supposed to increase by 20% automatically." So, they simply press Enter to accept the 240 value. However, Excel sees this as a change and increases the 240 by 20%, resulting in 288—not what you or the user intended.
This second consideration—user confusion—is the biggest potential problem with automatically changing what a user enters into the worksheet. A less confusing approach would be to have a well-defined input area for your workbook. The user puts figures into the input area and those figures remain as they input them. Then, in other cells or in your formulas, you do the adjustment by 20%.
This design approach (modifying the worksheet design for easy data entry) is potentially less confusing to the user than automatically changing what they entered in a worksheet cell. It also gets rid of a risk that is inherent with any macro-enabled workbook—the user could load the workbook without enabling macros, thereby ensuring that the figures are not adjusted as you intended.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12684) applies to Microsoft Excel 2007, 2010, and 2013.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Sometimes you have too much information in a cell and you need to "pare down" what is there to get to the info you really ...Discover More
Importing data into Excel that was generated in other programs can have some interesting side effects. For instance, you ...Discover More
If you have a large number of values in a column, you may want to move the values that meet specific criteria to another ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.