Automating the Budget Process

It's budget season again and we're back to trying to make the budget file better. Because of the nature of our business, we have sites that use different sets of GL account codes which makes creating a single budget file problematic. But my boss is a master of Excel. Most people would create three separate files but she is creating a single file to handle all situations.

The way she is doing this is by creating three types of GL group and assigning one of the GL group to each site. On the introductory tab, she chooses the site and the file pulls up the correct GL group that for that chosen site. The problem is she still has to go into each expense/revenue tab and filter on that GL group. There may be at least 15 tabs to filter, after which the filtering column also has to be hidden. This is the missing piece to her work.

My boss and I talked about this problem and she suggested a macro to put in her personal workbook that she can use to filter all 15+ tabs with the correct GL group.

With macros, there are actually different types of "macros" that can be created. When you use a macro recorder, a module is created and this is the most common one for beginners. When you want a macro to run upon opening a file, most of the time you use ThisWorkbook. If you want a macro to run when you make some kind of entry or change in a spreadsheet, commonly called an event, you use the Sheet's module. Then there is a class module and finally a user form.

See the different types of macros

For my boss' problem, I had to create a special macro that would initiate whenever she made a choice in a specific tab and specific cell. The event was choosing a site and once that event occurred, the macro will kick off and start filtering all of the tabs for the proper GL group for the site. Since this macro was going to reside in her personal workbook rather than in the budget file (to prevent the managers from accidentally initiating the process), the "event" instructions could not reside in the Sheet module. I had to use a combination of ThisWorkbook and a class module.

An example of the code is listed below. I'm not going to pretend I understand everything. I borrowed aspects of the code from a website (and I wished I had noted the site within the macro). Normally I structure the code with tabs but this WordPress editor always gets rid of the tabs/spaces so I can't indent the lines.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

ThisWorkBook code sitting in personal workbook

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' The following code must be placed in the "ThisWorkbook" module of your personal.xlsb

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Option Explicit
Private OurEventHandler As CAppEventHandler

Private Sub Workbook_Open()

' This section works with the App_SheetChange section and Class_Initialize found in (class module) CAppEventHandler
Set OurEventHandler = New CAppEventHandler

End Sub

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

The next series of code goes into the class module of your personal workbook.

Class Module called CAppEventHandler in your personal workbook

Option Explicit

' Declare our own reference to the Application object,
' WithEvents is needed to capture the events of the application object

Private WithEvents App As Application

' Whenever a new object of a class is instantiated, the _Initialize-Sub is called,
' that's why we use this Sub to get the reference to the current Application object

Private Sub Class_Initialize()
Set App = Application
End Sub

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

The next set of codes, which are still in the CAppEventHandler, starts whenever a site is chosen. This event happens in the "Title input" tab and in cell C11. If neither of these two conditions exist, then this part of the macro won't start.

Event Handling code sitting in CAppEventHandler in your personal workbook

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name <> "Title input" Then (Yellow highlight can be changed to fit a different situation)
Exit Sub
End If

Dim rngKeyCells As Range
Set rngKeyCells = Sh.Range("C11")

If Intersect(rngKeyCells, Target) Is Nothing Then
Exit Sub
End If

'If the two conditions are met, then perform the unhiding columns and unfiltering and then do the filtering and hiding columns. The code below would have to be changed to fit another situation.

Application.EnableEvents = False
Application.ScreenUpdating = False

'Ungroup columns
Sheets("Utilities").Activate
Sheets("Utilities").Range("A:C").Select
Selection.EntireColumn.Hidden = False

'Clear out prior filter
'AutoFilterMode has to do with the filter itself - creating the down arrows. FilterMode has to do with the filtering choices.
'Leave the filters in because they set the boundaries and helps with the filtering. The exclusion filter (not equal, <>) works with only 2 exclusion criteria.

If Sheets("Utilities").FilterMode = True Then
Sheets("Utilities").ShowAllData
End If

'Now apply filter; this kind of filter works with only 2 excluding criteria

If Sheets("Title input").Range("C27") = "xxxx" Then
Sheets("Utilities").Range("$A$3:$C$70").AutoFilter Field:=1, Criteria1:="<>x", Operator:=xlAnd, Criteria2:="<>#N/A"
ElseIf Sheets("Title input").Range("C27") = "yyyy" Then
Sheets("Utilities").Range("$A$3:$C$70").AutoFilter Field:=2, Criteria1:="<>x", Operator:=xlAnd, Criteria2:="<>#N/A"
ElseIf Sheets("Title input").Range("C27") = "zzzz" Then
Sheets("Utilities").Range("$A$3:$C$70").AutoFilter Field:=3, Criteria1:="<>x", Operator:=xlAnd, Criteria2:="<>#N/A"
Else
Sheets("Utilities").Range("$A$3:$C$70").AutoFilter Field:=1
End If

'Group columns
Sheets("Utilities").Range("A:C").Select
Selection.EntireColumn.Hidden = True
Sheets("Title input").Activate

Application.ScreenUpdating = True
Application.EnableEvents = True

MsgBox "Done!", vbInformation

End Sub