Double Entry Credit/Debit Macro

[MariosB]

I am trying to set up a workbook where I can keep track of all my personal expenses by entering each transaction and then being registered to each account. In my workbook I have created 5 sheets (Sheet1= Transaction, Sheet2= GL, Sheet3= Bank Account, Sheet4= Cash, Sheet5= Monthly Exp). In Sheet1 (Transaction) I've created a command button that brings up a UserForm, where I have some fields for entering details about my transaction, where they appear as headings also in the other 4 Sheets (ColumnB= Date, ColumnC= Description, ColumnD= Account From, ColumnE= Account To, ColumnF= Debit, ColumnG= Credit).

Say I make two transactions. First I withdraw $100 from my Bank Account and then I go to the supermarket and spend the $100. What I am trying to achieve is the following:

1. When I make the first transaction (money withdrawal) and I press OK from the UserForm I want:

• All the details from the UserForm to be transferred to Sheet2 (GL) under each respective column heading (Date, Description, Account From, Account To, Debit or Credit respectively)
• At the same time Sheet3 (Bank Account) to be credited (i.e. subtracted) with $100 and Sheet4 (Cash) to be debited (i.e. added) with the $100.

2. When I make the second transaction (supermarket spending) and I press OK from the UserForm I want:

• Again all the details from the UserForm to be transferred to Sheet2 (GL) under each respective column heading (Date, Description, Account From, Account To, Debit or Credit respectively)
• At the same time Sheet4 (Cash) to be credited (i.e. subtracted) with $100 and Sheet5 (Monthly Exp) to be debited with the $100.

Finally, when I enter each transaction I want it to be added to the next empty row of each respective sheet. I hope this is not too confusing

I am including the code of the UserForm below:

Private Sub CancelButton_Click()


Unload Me


End Sub




Private Sub ClearButton_Click()


Call UserForm_Initialize


End Sub


Private Sub OKButton_Click()


End Sub


Private Sub UserForm_Initialize()


'Empty DateTextBox
DateTextBox.Value = ""


'Empty DescriptionTextBox
DescriptionTextBox.Value = ""


'Empty FromComboBox
FromComboBox.Clear


'Empty DebitTextBox
DebitTextBox.Value = ""


'Empty CreditTextBox
CreditTextBox.Value = ""


'Fill FromComboBox
With FromComboBox
    .AddItem "Bank Account"
    .AddItem "Cash"
    .AddItem "Monthly Exp"


'Empty ToComboBox
ToComboBox.Clear


'Fill ToComboBox
With ToComboBox
    .AddItem "Bank Account"
    .AddItem "Cash"
    .AddItem "Monthly Exp"
 
'Set debit as default
DebitOptionButton.Value = True
End With


End With
End Sub

 

[FDARYABEE]

Hi, I need a similar excel workbook. Did you find a solution to your problem? Highly appreciated. Thanks

 

[Joe4]

Hi, I need a similar excel workbook. Did you find a solution to your problem? Highly appreciated. Thanks

MariosB has not logged in since 2017, so I doubt they are active on this board anymore.

If you have a question, it would probably be best to post it as a new question (in a new thread of your own).
That way it will appear in the "Unanswered threads" post that most people use to look for new unanswered questions.

 

[FDARYABEE]

MariosB has not logged in since 2017, so I doubt they are active on this board anymore.

If you have a question, it would probably be best to post it as a new question (in a new thread of your own).
That way it will appear in the "Unanswered threads" post that most people use to look for new unanswered questions.

Understood. Thank you