How can I only upload from UserForm controls which contain a value?

January 14, 2023

I have a user Form through which I am able to upload transactions into a cashflow worksheet. By using cDbl in two controls I am able to ensure that amounts are added to the Worksheet in a format that can be used in calculations. However, by nature these fields are mutually exclusive (Credit & Debit). cDbl requires a value to be populated in each control so I am looking for a method that will check the value of each of the two relevant controls and to ignore them when the value is Null

Private Sub cmdAddRecord_Click()
'Used to add new transation records to the database

    lastrow = Sheets("Spending Account").Range("A" & Rows.Count).End(xlUp).Row
    Cells(lastrow + 1, "A").Value = DTPicker1
    Cells(lastrow + 1, "B").Value = cboVendorDetails
    Cells(lastrow + 1, "C").Value = cboTransactionType
    Cells(lastrow + 1, "D").Value = CDbl(Me.txtTransactionAmountDebit)
    Cells(lastrow + 1, "E").Value = CDbl(Me.txtTransactionAmountCredit)
    Cells(lastrow + 1, "F").Value = cboTransactionStatus

With ActiveSheet
    Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
End With

    Unload Me

    frmRegularTransactions.Show

End Sub

I would welcome any solution

>Solution :

Private Sub cmdAddRecord_Click()
    'Used to add new transation records to the database
    Dim r As Long, sCredit As String, sDebit As String
    
    sDebit = Me.txtTransactionAmountDebit
    sCredit = Me.txtTransactionAmountCredit
    
    With Sheets("Spending Account")
        r = 1 + .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(r, "A").Value = DTPicker1
        .Cells(r, "B").Value = cboVendorDetails
        .Cells(r, "C").Value = cboTransactionType
        .Cells(r, "F").Value = cboTransactionStatus
        
        ' credit or debit
        If Len(sDebit) > 0 Then
            If Len(sCredit) > 0 Then
                MsgBox "Warning - Both Credit and Debit", vbExclamation
            Else
                .Cells(r, "D").Value = CDbl(sDebit)
            End If
        ElseIf Len(sCredit) > 0 Then
           .Cells(r, "E").Value = CDbl(sCredit)
        End If
          
        If r > 21 Then
            Application.Goto Reference:=.Cells(r - 20, "A"), Scroll:=True
        End If
    End With

    Unload Me
    frmRegularTransactions.Show

End Sub