How to Make the Super Starter Budget – Part 2
by Janet Dagostino, Savvy Spreadsheets
This is the second part of the How to Make the Super Starter Budget tutorial, where you will learn the macro portion.
Macros are used for four main features of the Super Starter Budget – 1) to quickly move from sheet to sheet with navigation buttons, 2) to add or delete expenses on the expense log, 3) to add, delete, or move categories, and 4) to “set” the budget month.
Open up your budget spreadsheet from Part 1, and let’s get started.
Create three rounded rectangular shapes in the upper right-hand corner of the Setup sheet. I’ve colored mine a light blue (198, 217, 241), added a 1/4 outline in dark grey (64, 64, 64), and added a small shadow at the bottom. In each button, enter the sheet names (Setup, Expenses, and Month Detail) in centered, black font, size 10. Name the shapes BtnSetup, BtnExpenses, and BtnMonth, respectively.
In your VBA Editor, insert a module called ModNavigation and in it a Sub called BtnNavHandler. Enter the code below in your Sub. This Sub will detect the name of the navigation button that’s clicked, and navigate to the appropriate sheet.
Now we have to connect the macro to the navigation buttons. Right-click on the Setup button and select “Assign Macro.” Then choose the macro “BtnNavHandler,” and click OK. Assign the same macro to the Expenses and Month Detail buttons. Copy and paste all three buttons onto the Expenses and Month Detail sheets.
On the Setup sheet, color the Setup button a darker blue (142, 180, 227) and remove the shading. This way, it will appear that the Setup button is selected.
Do the same for the Expenses and Month Detail sheet, coloring and removing the shading on the appropriate buttons (Expenses button on the Expenses sheet, Month button and the Month Detail sheet).
Click on the buttons and make sure they work!
Adding and Deleting Expenses
On the Expenses sheet, add two shapes, a minus sign and a plus sign right above the Category column. Color these in a dark green (79, 98, 40) and remove the outlines. These symbols will be turned into “buttons” that can be clicked to add or delete rows.
In your VBA Editor, insert a module called ModTables. We will be adding three Sub procedures in this module. The first Sub will be called FixTableBorders. The purpose of this Sub is to make sure the borders of our Expenses table always look good when we add new rows. Enter the following code.
Next add a Sub called AddTableRow, with the code below. This Sub checks to make sure the user’s cursor is inside the Expenses table, adds a row right below the one the user is on, and then fixes the borders by calling the FixTableBorders Sub.
Finally add the DeleteTableRow Sub, with the code below. This will again check to make sure the user has selected a row, and then it will delete the row.
Now that you’ve created your Subs, assign your macros to the symbols on the Expenses sheet. Assign the macro “DeleteTableRow” to the minus sign and the macro “AddTableRow” to the plus sign.
Play with the plus and minus buttons to see if they’re working .
Adding, Deleting, and Moving Categories
Go to the Setup sheet and add four shapes, a down arrow, an up arrow, a minus sign, and a plus sign, right above the Expense Categories section, to the right-hand side. Format these just like how you did the symbols on the Expenses table (dark green, no outline).
In cell A8, type BEG short for “beginning” and in cell A19 type END. Change the font color of these cells to the same light grey used for the background, so they are hidden. These “anchors” are needed for the macros that follow.
Turning the Application On/Off
Create a module called “ModAdmin.” In this module, add two subs called “AppOff” and “AppOn” with code below. The “AppOff” sub turns off various application features so that the macro can run faster without interruptions. The “AppOn” sub turns them back on so that Excel is back to its default settings. Future subs that we will make will call on these subs in order to run faster.
Get Row Info
Create another module called “ModSetup.” We will be creating several subs here, that will allow us to add, delete, and move up or down the category rows. First declare the variables in the code below. These variables will be used in our subs.
Add a sub called “GetRowInfo,” and enter the code below. This sub is used to assign values to the aforementioned variables.
Add another sub called “FixRowCounts,” with the below code. The purpose of this sub is to fix the enumeration next to categories. This is necessary because the numbers will get out of order when we add, remove, or move the categories rows around.
Add a sub called “AddRow.” This sub does quite a few things. First it checks to make sure the user is inside the category section, then it turns off some of Excel’s features by calling AppOff, populates our variables, checks to make sure there is no more than 10 rows, adds a row to the Setup and Month Detail tabs, fixes the category numbers by calling FixRowCounts, and finally reverts Excel back to its default state.
Add another sub called “AddRowOnTabs.” This sub is called by the AddRow sub, and provides explicit instructions on how exactly to “add a row.” Essentially what it’s doing is it’s copying the blank category row at the top and inserting it right below the row that the user is on. This sub is called twice by AddRow. It’s called once for the Setup sheet and then again for the Month Detail sheet.
Now you’ll add your “DeleteRow” sub. This sub does many of the same things that AddRow does, it checks to make sure the user has selected a category, turns off some of Excel’s settings, and populates the variables. It also pops up a message to double-check that the user really wants to delete a category and it makes sure that at least one category exists. If everything checks, the row is deleted on both the Setup and Month Detail sheets. Finally, the row counts are fixed and Excel’s settings are set back to normal.
Just like the AddRowOnTabs sub, there will be a “DeleteRowOnTabs” sub with the code below. This is a specific set of instructions that is called by DeleteRow to delete the proper row on the Setup and Month Detail sheets.
Moving Rows Up
Now it’s time to add the subs to move the categories up or down. Add a sub called “MoveRowUp.” This will do the appropriate first steps (check to make sure user is in the right spot, call AppOff, and call GetRowInfo), then call another sub MoveRowUpOnTabs for the Setup sheet and the Month Detail sheet, and then do our standard final steps (call FixRowCounts and AppOff).
Of course we also need to add the “MoveRowUpOnTabs” sub too. This sub cuts the entire row that the user has selected, and then pastes it on the row above.
Moving Rows Down
Finally we add the “MoveRowDown” sub. This sub is nearly identical to the MoveRowUp sub, except that of course it moves the row down instead of up.
Don’t forget the “MoveRowDownOnTabs” sub which will cut and paste the selected row to the row below it.
We are now done with the modules! Now you just have to assign the macros to the shapes on the Setup sheet. The down arrow will call MoveRowDown, the up arrow will call MoveRowUp, the minus sign will call DeleteRow and the plus sign will call AddRow.
Go ahead and test the symbols to make sure they are working.
Setting the Budget
Now we are ready for the last of the macro steps. On the ModAdmin module, Add a sub called Set Budget, with the code below. This sub will refresh the categories list on the lists tab. It works by filling fixing the formulas that pull our categories, which is important because these formulas will get messed up with all the adding, deleting, and moving that’s done on the Setup sheet. This is also why it’s important to click the “Set Budget” button each time a category is added, deleted, or moved.
Finally, add a rounded rectangle next to the title on the Setup sheet, that will serve as our button to trigger this macro. I’ve colored mine magenta (192, 28, 99) to match the title and used white as the font color. Assign the “Set Budget” macro to this shape.
As the final step, hide the chartdata and lists sheets.
Your interactive, macro-enabled budget spreadsheet is now complete! Use this budget template for any month you want. Just be sure to clear out the Expenses table with each new month, and adjust the title and budget time frame as necessary.