excel 根据条件自动选择选项(单选)按钮

ddrv8njm  于 2023-02-05  发布在  其他
关注(0)|答案(1)|浏览(217)

我有两组单选按钮.见下图.

其中一组用于交易类型,另一组用于交易编号
一天总共允许6笔交易。其中3笔用于取款,其他3笔用于存款
我正在使用下面的vba代码来自动选择下一个单选按钮,如果一个是使用。但没有运气。

Sub OBWithdrawal_Click()

Dim OBWithdrawal, OBDeposit, OB1st, OB2nd, OB3rd As OptionButton

    'transaction type

Set OBWithdrawal = Sheet1.Shapes("OBWithdrawal").OLEFormat.Object
Set OBDeposit = Sheet1.Shapes("OBDeposit").OLEFormat.Object

    'transaction number

Set OB1st = Sheet1.Shapes("OB1st").OLEFormat.Object
Set OB2nd = Sheet1.Shapes("OB2nd").OLEFormat.Object
Set OB3rd = Sheet1.Shapes("OB3rd").OLEFormat.Object

If Sheet1.Range("G24").Value = "#NUM" Then
OB1st.Value = True

    If Sheet1.Range("G24").Value = 1 Then
    OB2nd.Value = True

        If Sheet1.Range("G24").Value = 2 Then
        OB3rd.Value = True

            If Sheet1.Range("G24").Value = 3 Then
            OB1st.Value = False
            OB2nd.Value = False
            OB3rd.Value = False
            MsgBox "You have only 3 withdrawal are allowed in a single day." & vbNewLine & ""

            End If
        End If
    End If
End If
End Sub

我没有收到任何错误信息。所以我不知道我做错了什么。
请帮帮忙

p4rjhz4m

p4rjhz4m1#

**1.**始终单独声明变量。如果您将它们声明为Dim OBWithdrawal, OBDeposit, OB1st, OB2nd, OB3rd As OptionButton,则只有最后一个OB3rd将被声明为OptionButton。其余变量将被声明为Variant
**2.**您可以使用单个IF-ELSEIF-ENDIF语句来处理所有这些条件。
**3.**此过程适用于Withdrawal。类似地,为Deposit创建。或者,您可以为两者创建一个公共过程,然后使用Application.Caller确定哪个是“调用”选项按钮,然后执行相关代码。

这就是你想要的吗?

Option Explicit

Sub OBWithdrawal_Click()
    Dim OBWithdrawal As OptionButton
    Dim OB1st As OptionButton
    Dim OB2nd As OptionButton
    Dim OB3rd As OptionButton

    Set OBWithdrawal = Sheet1.Shapes("OBWithdrawal").OLEFormat.Object
    
    With Sheet1
        Set OB1st = .Shapes("OB1st").OLEFormat.Object
        Set OB2nd = .Shapes("OB2nd").OLEFormat.Object
        Set OB3rd = .Shapes("OB3rd").OLEFormat.Object

        If .Range("G24").Value = "#NUM" Then
            OB1st.Value = True
        ElseIf .Range("G24").Value = 1 Then
            OB2nd.Value = True
        ElseIf .Range("G24").Value = 2 Then
            OB3rd.Value = True
        ElseIf .Range("G24").Value = 3 Then
            OB1st.Value = False
            OB2nd.Value = False
            OB3rd.Value = False
            MsgBox "You have only 3 withdrawal are allowed in a single day." & vbNewLine & ""
        End If
    End With
End Sub

编辑

这是我提到的第三种方法。将此代码分配给WithdrawalDeposit按钮。这对两者都有效。

Option Explicit

Sub OBWithdrawalDeposit_Click()
    Dim OptBtn As OptionButton
    Dim OB1st As OptionButton
    Dim OB2nd As OptionButton
    Dim OB3rd As OptionButton
    Dim OptBtnName As String
    Dim TrnType As String
    
    OptBtnName = Application.Caller
    
    '~~> If Withdrawal was selected
    If OptBtnName = "OBWithdrawal" Then
        Set OptBtn = Sheet1.Shapes("OBWithdrawal").OLEFormat.Object
        TrnType = "Withdrawals"
    ElseIf OptBtnName = "OBDeposit" Then '<~~ If Deposit was selected
        Set OptBtn = Sheet1.Shapes("OBDeposit").OLEFormat.Object
        TrnType = "Deposits"
    Else
        MsgBox "This procedure was not called the right way"
        Exit Sub
    End If
    
    With Sheet1
        Set OB1st = .Shapes("OB1st").OLEFormat.Object
        Set OB2nd = .Shapes("OB2nd").OLEFormat.Object
        Set OB3rd = .Shapes("OB3rd").OLEFormat.Object

        If .Range("G24").Value = "#NUM" Then
            OB1st.Value = True
        ElseIf .Range("G24").Value = 1 Then
            OB2nd.Value = True
        ElseIf .Range("G24").Value = 2 Then
            OB3rd.Value = True
        ElseIf .Range("G24").Value = 3 Then
            OB1st.Value = False
            OB2nd.Value = False
            OB3rd.Value = False
            MsgBox "You are allowed only 3 " & TrnType & " in a single day."
        End If
    End With
End Sub

相关问题