Creating a Button Contingent on Radio Buttons Selected

Question

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

You want Worksheets("Calculator") (plural) - not Worksheet("Calculator") — braX
– braX, Commented Jun 10 at 22:14

VBasic2008 · Accepted Answer · 2025-06-11 01:03:28Z

2

Option Buttons

Form Controls

To reference a member of the Worksheets collection, you need to use Worksheets(...) as mentioned by braX in your comments. To see the difference, in a sub write Worksheet(. Nothing will happen. Remove or out-comment the line. Then try Worksheets( and you will see that IntelliSense kicks in.
Shapes don't have a Value property. In a sub, write the line Dim shp As Shape: shp.v and observe how it isn't offered by IntelliSense.
When selected, an Option Button's value is 1 (not True). Otherwise, it's -4146 (not False).

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

A With statement often makes the code more readable. A Select Case statement is often more appropriate than using (multiple) ElseIf statements (not so much in this case).

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

If you decide to use ActiveX controls instead of Form controls, the same would look like this:

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

The values of these controls are True or False. Also, note how their generic names are different (no spaces).

edited Jun 11 at 1:03

answered Jun 11 at 0:56

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FunThomas Jun 11 at 15:43

While you are technically right with values 1 and -4146, you should use this. Use xlOn and xlOff instead.

VBasic2008 Jun 11 at 15:58

@FunThomas Thanks for the reminder. Forgot to look it up.

Collectives™ on Stack Overflow

Creating a Button Contingent on Radio Buttons Selected

1 Answer 1

Option Buttons

Form Controls

ActiveX Controls

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option Buttons

Form Controls

ActiveX Controls

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related