Coding Command Button to Display Message after Performing Calculations

Home More Excel (VBA) Training Videos

Dear Mr Dinesh

Thanks to your videos I got used little by little to the use of the userform in excel 2010. I found it very interesting and I even (after a long job) realized a userform for entering accounting data (debit and credit). The userform works very good and all the data is transferred to my worksheet when I press the bouton “Add data” . I also created in my form a second bouton to exit the userform. This bouton is working very fine. To tell you the truth I have no  idea of coding in VBA (I am an accountant)
So I want when I press the bouton “Exit” a messagebox inform me that the total of “debits” is not equal to the total of “credits”
Of course if there’s a difference in my worksheet between the row debit and credit. If there’s no difference there’s no msgbox
Thank you an advance

Yours Hichem

The comple Excel VBA code is given below:
Private Sub CommandButton1_Click()
erow = Feuil1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
Cells(erow, 1) = TextBox1
Cells(erow, 2) = TextBox2
Cells(erow, 3) = TextBox3
Cells(erow, 4) = cmbsens
Cells(erow, 5) = TextBox4
Cells(erow, 6) = TextBox5
'Clear the data from the form
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.cmbsens.Value = ""
End Sub

Private Sub CommandButton2_Click()
Dim row As Integer
Dim total, debit, credit As Single

row = 2
total = 0
debit = 0
credit = 0

Do While Cells(row, 5).Value <> ""
total = total + Cells(row, 5).Value
If Cells(row, 5) < 0 Then
debit = debit + Cells(row, 5).Value
End If

If Cells(row, 5) > 0 Then
credit = credit + Cells(row, 5).Value
End If
row = row + 1
If debit <> credit Then
MsgBox "Total of debit & credit not equal! " & "Debit = " & Abs(debit) & " " & "Credit = " & credit & " the difference is " & (credit + debit)
End If

End Sub

Private Sub UserForm_Initialize()
With cmbsens
.AddItem "Débit"
.AddItem "Crédit"
End With
End Sub

Note: You can display the total, total debits and total credits in cells of your choice.

Watch this training video on youtube

Home More Excel (VBA) Training Videos