More Macro Fun

This is a continuation of a prior post but this time, I will talk about a really interesting kind of macro that I have recently been learning. I knew that you could record macros, you could have a button for starting macros, you could use ctrl+key to start a macro, you could have a macro that would automatically start upon opening the file and another macro run automatically upon closing a file, but I didn’t know that you could have a macro start running just because you make a change somewhere in the worksheet. These kinds of macros have some really great possibilities here.

If you right-click a tab, you will see an option called “View code” which will lead you to the VBA. I believe that most of the macros you create, either through the recorder or through the developer ribbon are created in the Modules. When you do the “View code” method, I believe it leads you to the Private Sub in the Sheet1 (or whatever your tab is titled). Then there is the “This Workbook” object where the automatic open codes can be found and you can access via Alt-F11. So there are at 3, possibly 4, different places for macros to reside. Most of the time your macros will be in the Modules.

If you right-click your tab and pick “View code”, you can create a private sub that will start codes upon a change in the tab. I think the private sub is strictly for that tab; macros from elsewhere cannot use that private sub. With these private subs, you can start a macro just by selecting a cell. Or you can start a macro by changing something in a cell. The general set up for these private subs is the Sheet1 object will be set to Worksheet (rather than General) in the upper left hand corner of the VBA window and in the upper right, instead of Declarations or the name of the macro, it will say SelectionChange (for when selecting a cell will cause a macro to run) or just Change (when a cell is changed).

The screenshot below shows the 4 places where macros can reside. Ignore the 4th one – FUNCRES.XLAM as I don’t know what is going on there…yet. The upper left one is the Sheet code where you can see the private sub for calculate. Below that is the Module code where the typical macros reside. The one on the right is ThisWorkbook code where the automatic open macro resides. (The automatic close macro will be found in the Module code.)

See how in the upper left hand corner of the Sheet code there is Worksheet rather than General? In order to use private subs, this section must be set to Worksheet. Once you do that, the box over to the right then allows you to pick what type of action you want to take: SelectChange, Change or Calculate. They won’t show up until you pick Worksheet in the left box.

The sample above is shown for the Calculate method of change but I want to talk first about the SelectionChange or just Change method. These are easier to understand. The Change method will probably happen more frequently than the Selection Change but they both utilizes the same syntax. Both SelectionChange and Change utilizes just the Sheet code whereas Calculate utilizes all three (Sheet, Module and ThisWorkbook).

The syntax is generally as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$A$4” Then

(For SelectionChange, just replace Change with SelectionChange)

I’ve been setting mine up as: if cell A4 changes (or whatever cell you choose), then do the steps following Then. There are various permutations for setting up Target:

If Target.Column = 1 Then         This one is if any cell in column A changes, then perform the actions after Then.

If Intersect(Target, Range(“A1:A10”)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

The above is a little more complicated. I think it’s saying if cells A1 through A10 are blank or the cells count is greater than 1, then exit the macro. This is new – I’ve never used Intersect or Nothing so I am not certain what that line is doing.

So that is the general initial set up for Change and SelectionChange. After the THEN, you just write-up the rest of the macro detailing what action you want taken.

Now for the Calculate action, notice how simpler the initial statement is: Private Sub Worksheet_Calculate(). But, this action does require adding some lines to the Sheet, Module and ThisWorkbook and you need to close and then re-open the file once you set up the macro.

The lines you need to add are:

Sheet: If Range(“$a$1”).Value<>PrevVal Then ($a$1 can be any cell)
Module: Public PrevVal as Variant (this is to be located in the General / Declarations section)
ThisWorkbook:   Private Sub Workbook_Open()

PrevVal = Sheets(“your sheet’s name”).Range(“$a$1).Value
End Sub

These kinds of macros open up a whole new world of possibilities.