VBA help

[ryancgarrett]

I have a form in an accounting program I'm working on where a user can input a transaction. The form allows the user to select an account from a combo box, input an amount, and using an option button select whether it is a debit or a credit. In accounting debits must equal credits before you may go on, so I am trying to make it so the "Submit" button is disabled until the debits and the credits are the same. (I wish I knew of a way to put my form up here; if anyone knows how please tell)

At the bottom of the form I want the total debits and the total credits, each textbox updating when an option button is clicked. Here is my code which doesn't seem to work at all...

Sub formBalance()
    Dim SumofDebits As Long
    Dim SumofCredits As Long
    
    SumofDebits = frmTransactionEntry.optDebit1.Value + frmTransactionEntry.optDebit2.Value + frmTransactionEntry.optDebit3.Value + frmTransactionEntry.optDebit4.Value + frmTransactionEntry.optDebit5.Value + frmTransactionEntry.optDebit6.Value
    SumofCredits = frmTransactionEntry.optCredit1.Value + frmTransactionEntry.optCredit2.Value + frmTransactionEntry.optCredit3.Value + frmTransactionEntry.optCredit4.Value + frmTransactionEntry.optCredit5.Value + frmTransactionEntry.optCredit6.Value

    FormatCurrency (frmTransactionEntry.tbxAmount1.Value)
    FormatCurrency (frmTransactionEntry.tbxAmount2.Value)
    FormatCurrency (frmTransactionEntry.tbxAmount3.Value)
    FormatCurrency (frmTransactionEntry.tbxAmount4.Value)
    FormatCurrency (frmTransactionEntry.tbxAmount5.Value)
    FormatCurrency (frmTransactionEntry.tbxAmount6.Value)
    
    
        If SumofDebits <> 0 And SumofDebits = SumofCredits Then
            frmTransactionEntry.cbtnSubmit.Enabled = True
            frmTransactionEntry.tbxSumOfDebits.Visible = True
            frmTransactionEntry.tbxSumOfCredits.Visible = True
        ElseIf SumofCredits <> 0 And SumofDebits = SumofCredits Then
            frmTransactionEntry.cbtnSubmit.Enabled = True
            frmTransactionEntry.tbxSumOfDebits.Visible = True
            frmTransactionEntry.tbxSumOfCredits.Visible = True
        End If
    
End Sub

Note: If the debits or credits are equal to zero the textboxes showing the sum should be blank.

 

[CWatts]

One thing you can do is have a change function based on your text input elements. When the data is changed, it compares the debits to the credits, and then you can have a statement such as:

If Debits = Credits Then
     PlaceholderCommandButton.Visible = False
     SubmitCommandButton.Visible = True
End If

So you have a placeholder command with a flat look that when pressed gives the user a message box that says "Balance Debits and Credits" and when the numbers match, that one is hidden and the good one is show, but that one is displayed with the raised look and that can do your math.

I hope that helped get the ball rolling, sorry if it comes across as just late-night babbling.

 

[AlphaFrog]

Sub formBalance()
    
    Dim SumofDebits As Currency
    Dim SumofCredits As Currency
    Dim i As Integer
    
    With frmTransactionEntry
        For i = 1 To 6
            If IsNumeric(.Controls("tbxAmount" & i).Value) Then
                .Controls("tbxAmount" & i).Value = FormatCurrency(.Controls("tbxAmount" & i).Value)
                If .Controls("optDebit" & i).Value = True Then
                    SumofDebits = SumofDebits + .Controls("tbxAmount" & i).Value
                ElseIf .Controls("optCredit" & i).Value = True Then
                    SumofCredits = SumofCredits + (.Controls("tbxAmount" & i).Value + 0)
                End If
            End If
        Next i
        
        If SumofDebits = 0 Or SumofCredits = 0 Then
            .tbxSumOfDebits.Value = ""
            .tbxSumOfCredits.Value = ""
            .cbtnSubmit.Enabled = False
        Else
            .tbxSumOfDebits.Value = FormatCurrency(SumofDebits)
            .tbxSumOfCredits.Value = FormatCurrency(SumofCredits)
            .cbtnSubmit.Enabled = SumofDebits = SumofCredits
        End If
    End With
End Sub

 

[ryancgarrett]

That is exactly what I needed! I've tried different things for hours and you nailed it! Thanks so much!