There’s more than one way to get your Excel macro to run when there’s an event like changes to your worksheet.
You can use a special macro name which tells Excel when to trigger that code.
By Office Watch reader, Howard Tanner
There’s more than one way to get your Excel macro to run when there’s an event like changes to your worksheet.
You can use a special macro name which tells Excel when to trigger that code.
Worksheet_Calculate – will run whenever Excel recalculates the worksheet. That would seem to be enough, however Excel is choosy about when it recalculates, for performance reasons. As a result it might not be enough for some purposes.
Worksheet_Change – lets you run code whenever there is a change in the worksheet, and better still you can specify which cells have to change in order to run the code.
I’ll demonstrate Worksheet_Change with this simple example. I’ll create a spreadsheet that displays the color that you get with three RGB values. The calculation of the color is simple because there’s an Excel function to do it.
If you use the Worksheet_Calculate event, it fires whenever Excel decides to recalculate the worksheet. Unfortunately for this exercise, Excel does not perform a calculation if there are no formulas in the worksheet.
So my exercise uses the Worksheet_Change event instead – I can tell Excel to run some code whenever any one of the three RGB cells is changed. I’ll also demonstrate named ranges and show you one reason why they are very useful.
Each event has specific parameters it needs. Check Excel’s help for the exact parameters. Worksheet_Change is called with a value specifying the cell(s) that was changed. So when we change one of the RGB values, we can change the target cell(s) to that color. Note that in this simple example, we don’t check what was actually changed, we just set the color.
Event handling code needs to be attached to the spreadsheet itself, not in a separate module. Here’s the code for this exercise:
Private Sub Worksheet_Change(ByVal R As Range)
Range(“TestColor”).Interior.Color = RGB(Range(“A2”), Range(“B2”), Range(“C2”))
Range(“TestColor”).Font.Color = vbWhite End Sub
As I said, this code fires every time a value on the worksheet changes. It sets the cell named TestRange to the color defined by cells A2, B2 and C2. Since white text shows better in most situations, I set the font color to white. I set it in code because my original code sets the color to the RGB complement, but that’s beyond our scope in this exercise.
Note that I used a named range so I can use it in my formulas and VBA code. This allows me to change the reference to be a block of cells instead of a single cell, and my code doesn’t change.
The RGB function creates a color value (long integer) from three individual color values (Red, Green and Blue). Each value must be an integer (no decimal) between 0 and 255. vbWhite is a constant provided by VBA and has the color value for white.
To try this, open a new spreadsheet. Enter R into A1, G into B1 and B into C1. Enter the word TestColor into A5 (or anywhere, since we will name this cell).
Click on that TestColor cell, and select Insert, Name, Define. The default ‘TestColor’ took the name from the first cell in the range you selected before you took the menu option. In our case, we want to name this cell TestColor, so the default is exactly what we want. So just click Add, then OK. You should now see the name TestColor instead of A5 as the current cell:
Now that the spreadsheet is done, it’s time to add the code. Press Alt-F11 to open the VBA editor. Alt-F11 actually toggles between the VBA editor and Excel. It also brings up the VBA editor in any Office product.
In your project window (in the upper left by default) the current workbook will at the top – called ‘Sheet1’ or whatever you named the workbook when you saved it.
Any other open workbooks and add-ins will also be listed. Double-click Sheet1 in the current workbook to open the code window for that worksheet, and paste the above code into the code window.
That’s it! This code will now fire any time Excel detects a change in the cells.
Now let’s test. Press Alt-F11 to get back to Excel and enter some values for R, G and B, like this:
Notice that as you enter each value, the color for TestColor changes. Cool! If it doesn’t work – go back and carefully check your code and references.
Now let’s change TestColor so that more cells are colored. Select Insert, Names, Define and click on TestColor:
Change the “Refers to” so it’s =Sheet1!$A$5:$C$5. Alternatively, click in the “Refers to” box, then drag in the worksheet to highlight the new range. Click OK to save the change. To see the new results, change one of the values.
By using a named range for TestColor instead of a cell reference, we were able to change what the name represents in one place and have that reflected all through the worksheet. Cool, huh?
