Coding Command Button to Display Message after Performing Calculations

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 button “Add data” . I also created in my form a second button to exit the userform. This button 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 button “Exit” a message box inform me that the total of “debits” is not equal to the total of “credits”, 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
Loop
If debit <> credit Then
MsgBox “Total of debit & credit not equal! ” & “Debit = ” & Abs(debit) & ” ” & “Credit = ” & credit & ” the difference is ” & (credit + debit)
End If
UserForm1.Hide

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 the video:

 


One thought on “Coding Command Button to Display Message after Performing Calculations

Leave a Reply

Your email address will not be published. Required fields are marked *