excel 如何仅从包含值的UserForm控件上载？

3phpmpom 于 2023-01-18 发布在其他

关注(0)|答案(1)|浏览(101)

我有一个用户表单，通过它我可以将事务处理上载到现金流量工作表中。通过在两个控件中使用cDbl，我可以确保金额以可用于计算的格式添加到工作表中。但是，这些字段本质上是互斥的（贷方和借方）。cDbl需要在每个控件中填充一个值，因此我正在寻找一个方法，该方法将检查两个相关控件中的每个控件的值，并在出现以下情况时忽略它们该值为空

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

我欢迎任何解决办法

excel

来源：https://stackoverflow.com/questions/75119781/how-can-i-only-upload-from-userform-controls-which-contain-a-value

1条答案

按热度按时间

jw5wzhpr1#

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

赞(0）回复(0）举报 2023-01-18

我来回答

excel 如何仅从包含值的UserForm控件上载？

1条答案

相关问题

热门标签

最新问答