Automatically select option (Radio) buttons based on condition-CodePudding

I have two groups of radio buttons. see below image.

where one group for transaction type and another for transaction number.

total 6 transactions are allowed in a day. where 3 for withdrawals and other three for deposits.

I am using below vba code to auto select next radio button if one is used. but no luck.

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

I did not received any error messages. so I cant figure out where I m doing wrong.

please help

CodePudding user response：

1. Always declare variables separately. If you declare them as Dim OBWithdrawal, OBDeposit, OB1st, OB2nd, OB3rd As OptionButton, then only the last one which is OB3rd will be declared as OptionButton. Rest will be declared as Variant

2. You can use a single IF-ELSEIF-ENDIF statement to handle all those criteria.

3. This procedure is for Withdrawal. Similarly, create for Deposit. Alternatively, you can create a common procedure for both and then use Application.Caller to identify which is the "calling" option button and then execute the relevant code.

Is this what you are trying?

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

EDIT

This is the 3rd way that I was talking about. Assign this code to both the Withdrawal and Deposit button. This will work for both.

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