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
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.Value
property. In a sub, write the line Dim shp As Shape: shp.v
and observe how it isn't offered by IntelliSense.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
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
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
True
or False
. Also, note how their generic names are different (no spaces).