Budget Planning with Excel: The Expense Form

The biggest time-saver in my hack of Excel is the expense form I created:


You can design forms like these in the Visual Basic Editor. (Here's a tutorial.)

When I enter a receipt into the budget (yes, we save all our receipts; I know it sounds like a pain, but it's helpful), I select the column of the account that I paid with and the row that shows the date. Then I launch the expense form (I assigned it to Ctrl + E).

The picture above reflects a Target receipt from February 1. We bought items in three budget categories: A supplement that falls under health expenses ("Out-of-pocket"), conditioner ("Toiletries") and soy milk ("Groceries"). I enter the total bill in the first box, assign the expense to at least one budget category in the "Budget" frame, and add a comment (usually the name of the store where the purchase was made).

The form is particularly helpful for split checks like this one. When I add second and third budget categories, the form calculates how much of the bill remains in the first category. This means I can just enter the retail prices for the soy milk ($2.59) and conditioner ($3.14), and the form will deduct the remainder of the bill from Out-of-pocket. (This ends up sticking one category with all the tax, but I'm content with that level of imprecision.)

When I hit OK, my Visual Basic code takes the information from the form and writes it to the proper spots on the spreadsheet: the expense is taken out of the account, the proper budget categories are docked, and the comment is created. Before 2010, I was doing each of these actions separately. Splitting a receipt like this one into multiple budget categories required redundant comments on each cell involved.

So, what does this code look like? Something like this:

Dim expense, c2, c3, c4, c5 As Currency

Private Sub cbOK_Click()
'Write the textbox contents to their correct cell.
   Dim r, i As Long
   Dim cExpense, cCat1, cCat2, cCat3, cCat4, cCat5 As Long
   Dim s As Worksheet
   Dim txtArr(3)
   Dim cbArr(3)
   Dim colArr(3)

   Set s = ActiveSheet
   r = ActiveCell.Row
   cExpense = ActiveCell.Column

'Choose column based on combobox contents.
'If there's an amount for the category, write the amount to the correct column.
   If Me.cbCategory1.Value = vbNullString Then
      MsgBox "Specify a budget category"
      Me.cbCategory1.SetFocus
      Exit Sub
   Else
      cCat1 = ReturnColumnNumber(Me.cbCategory1.Text)
      If s.Cells(r, CInt(cCat1)).Value <> vbNullString Then
         MsgBox "Please create a new line for this transaction."
         Exit Sub
      End If
      s.Cells(r, CInt(cCat1)).Value = CCur(-1 * Me.txtCategory1.Value)
   End If
'Try using arrays of controls to cut down on code for categories 2-5
   Set txtArr(0) = Me.txtCategory2
   Set txtArr(1) = Me.txtCategory3
   Set txtArr(2) = Me.txtCategory4
   Set txtArr(3) = Me.txtCategory5

   Set cbArr(0) = Me.cbCategory2
   Set cbArr(1) = Me.cbCategory3
   Set cbArr(2) = Me.cbCategory4
   Set cbArr(3) = Me.cbCategory5

   For i = 0 To UBound(txtArr)
      If txtArr(i).Value <> vbNullString Then
         If cbArr(i).Value = vbNullString Then
            MsgBox "Specify a budget category."
            cbArr(i).SetFocus
            Exit Sub
         Else
            colArr(i) = ReturnColumnNumber(cbArr(i).Text)
            If s.Cells(r, CInt(colArr(i))).Value <> vbNullString Then
               MsgBox "Please create a new line for this transaction."
               Exit Sub
            End If
            s.Cells(r, CInt(colArr(i))).Value = CCur(-1 * txtArr(i).Value)
         End If
      End If
   Next i

'Write the expense
   s.Cells(r, cExpense).Value = CCur(-1 * Me.txtExpense.Value)

'Comments
   If Me.cbComment.Value <> vbNullString Then
      ActiveCell.AddComment (Me.cbComment.Value)
   End If

   Unload Me
End Sub

Private Sub cbCancel_Click()
   Unload Me
End Sub

Private Sub txtExpense_Change()
   ConvertToCurrency
   Me.txtCategory1.Value = expense - c2 - c3 - c4 - c5
End Sub

Private Sub txtCategory2_Change()
   ConvertToCurrency
   Me.txtCategory1.Value = expense - c2 - c3 - c4 - c5
End Sub
'There's an identical subroutine for the text boxes for categories 3, 4, and 5. Not shown in this post.

Private Sub UserForm_Initialize()
   Dim i As Integer
   Dim catList As String
   Dim catArr As Variant
'Fill in date
   Me.txtDate.Text = ActiveSheet.Range("A" & ActiveCell.Row & ":A" & ActiveCell.Row).Text
'Populate budget category comboboxes
   catList = ListBudgetCategories
   catArr = Split(catList, "|")
   For i = 0 To UBound(catArr)
      Me.cbCategory1.AddItem (catArr(i))
      Me.cbCategory2.AddItem (catArr(i))
      Me.cbCategory3.AddItem (catArr(i))
      Me.cbCategory4.AddItem (catArr(i))
      Me.cbCategory5.AddItem (catArr(i))
   Next i
'Populate comment combobox
   catList = ListComments
   catArr = Split(catList, "|")
   For i = 0 To UBound(catArr)
      Me.cbComment.AddItem (catArr(i))
   Next i
End Sub

Private Function ConvertToCurrency()
   Dim txtArr(4)
   Dim valArr(4)
   Dim i As Long
'Validate numeric entry and set variables equal to box contents.
   If TypeName(Me.ActiveControl) = "TextBox" Then
      With Me.ActiveControl
         If Not IsNumeric(.Value) And .Value <> vbNullString Then
            .Value = vbNullString
         End If
      End With
   End If
'For things in Budget frame, need to refer to the control within the frame control.
   If TypeName(Me.ActiveControl) = "Frame" Then
      With Me.ActiveControl.ActiveControl
         If Not IsNumeric(.Value) And .Value <> vbNullString Then
            .Value = vbNullString
         End If
      End With
   End If

'Cycle through array of textboxes
   Set txtArr(0) = Me.txtExpense
   Set txtArr(1) = Me.txtCategory2
   Set txtArr(2) = Me.txtCategory3
   Set txtArr(3) = Me.txtCategory4
   Set txtArr(4) = Me.txtCategory5

   For i = 0 To UBound(txtArr)
      If txtArr(i).Value = vbNullString Then
         valArr(i) = 0
         Else: valArr(i) = txtArr(i).Value
      End If
   Next i
   expense = CCur(txtArr(0))
   c2 = valArr(1)
   c3 = valArr(2)
   c4 = valArr(3)
   c5 = valArr(4)
End Function

Function ListBudgetCategories() As String
   Dim i As Long
   Dim catList As String
'If a column is green in row 4, that means it's a budget category, so add it to the combobox.
   For i = 1 To 55
      If ActiveSheet.Cells(4, i).Interior.Color = 14545386 Then
         catList = catList & "|" & ActiveSheet.Cells(2, i).Value
      End If
   Next i
   catList = Replace(catList, "|", "", , 1)
   ListBudgetCategories = catList
End Function

Function ListComments() As String
'Populate the Comment combobox with the comments that already exist on the page.
   Dim comList As String
   Dim com As Comment

   For Each com In ActiveSheet.Comments
      comList = comList & "|" & com.Text
   Next
   comList = Replace(comList, "|", "", , 1)
   comList = Replace(comList, Chr(10), "")
   ListComments = comList
End Function