excelvba

Creating a Button Contingent on Radio Buttons Selected


I have 2 sub procedures and 2 radio buttons. I want to create a macro that, when clicked, executes the Email_No_New if the first radio button is selected or executes Email_New if the second radio button is selected (the radio button selection are mutually exclusive.) I'm getting a "Sub or Function not defined" error on the 3rd line.

Sub GenerateEmail_Click()
    Dim ws As Worksheet
    Set ws = Worksheet("Calculator")
    If ws.Shapes("Option Button 1").Value = True Then
           Call EMail_No_New
    Else
        If ws.Shapes("Option Button 2").Value = True Then
            Call EMail_New
        End If
    End If
End Sub

Solution

  • Option Buttons

    Form Controls

    A Quick Fix

    Sub GenerateEmail_Click_QF()
        Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Calculator")
        If ws.OptionButtons("Option Button 1").Value = 1 Then
            Email_No_New
        ElseIf ws.OptionButtons("Option Button 2").Value = 1 Then
            Email_New
        Else
            MsgBox "No option button selected!", vbExclamation
        End If
    End Sub
    

    An Improvement

    Sub GenerateEmail_Click()
        With ThisWorkbook.Sheets("Calculator")
            Select Case 1 ' 1 (selected); -4146 (not selected)
                Case .OptionButtons("Option Button 1").Value: Email_No_New
                Case .OptionButtons("Option Button 2").Value: Email_New
                Case Else: MsgBox "No option button selected!", vbExclamation
            End Select
        End With
    End Sub
    

    ActiveX Controls

    Sub GenerateEmail_Click_ActiveX()
        With ThisWorkbook.Sheets("Calculator")
            Select Case True ' True (selected); False (not selected)
                Case .OLEObjects("OptionButton1").Object.Value: Email_No_New
                Case .OLEObjects("OptionButton2").Object.Value: Email_New
                Case Else: MsgBox "No option button selected!", vbExclamation
            End Select
        End With
    End Sub