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:

 


2 thoughts on “Coding Command Button to Display Message after Performing Calculations

  1. Raghu Prabh

    I modified the above code as follows
    Option Explicit
    Private Sub CommandButton1_Click()
    Dim eRow As Long

    eRow = Sheet1.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
    ‘If member selects debits can still input positive amount.
    If Cells(eRow, 4).Value = “Debit” Then
    Cells(eRow, 5) = TextBox4 * (-1)
    Else
    Cells(eRow, 5) = TextBox4
    End If
    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 UserForm_Initialize()
    With cmbsens
    .AddItem “Debit”
    .AddItem “Credit”
    End With
    End Sub
    Private Sub cmdClose_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
    credit = credit + Cells(row, 5).Value
    End If
    row = row + 1
    Loop
    If debit credit Then
    MsgBox “Total of debit & credit not equal! ” & vbCrLf
    MsgBox “Debit = $” & Abs(debit) & ” ” & “Credit = $” & credit & vbCrLf
    If Abs(debit) > credit Then
    MsgBox ” The DEBIT is $” & (credit + debit)
    ElseIf credit > Abs(debit) Then
    MsgBox ” The CREDIT is $” & (credit + debit)
    End If
    End If
    UserForm1.Hide
    End Sub

    Private Sub cmdQuit_Click()
    Application.Quit
    End Sub

    Reply

Leave a Reply

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