请求VBA技术支持：根据选项按钮状态显示/隐藏命令按钮

Hey there! Let's get your option buttons and command button working correctly. I see you're trying to show the submit-to-email command button only when the "Yes" option is selected, and hide it when "No" is chosen. Let's walk through what's wrong with your current code and how to fix it.

What's Off in Your Current Code #

• Wrong Property: Your first macro uses Enabled = True/False—this only controls whether the button can be clicked, not whether it's visible. You need to use the Visible property instead.
• Conflicting Option Button Logic: The OptionButton78_Change macro is manually forcing the state of both option buttons, which clashes with Excel's built-in behavior for radio buttons (they should automatically deselect each other when part of the same group). This is causing unnecessary state confusion.
• Unreliable Worksheet Event: The Worksheet_Change macro relies on cell V1's value, but unless your option buttons are directly bound to that cell, this event won't trigger when you select an option. Plus, targeting the button via its shape name is less reliable than referencing the control directly.

Corrected Code Solution #

First, delete your OptionButton78_Change and Worksheet_Change macros—we don't need them. Then use these simplified click event macros for your two option buttons (assuming OptionButton78 is "Yes" and OptionButton79 is "No"):

Basic Version

' Triggered when "Yes" option is clicked
Sub OptionButton78_Click()
    ' Show the command button
    Sheets("New Req").CommandButton65.Visible = True
End Sub

' Triggered when "No" option is clicked
Sub OptionButton79_Click()
    ' Hide the command button
    Sheets("New Req").CommandButton65.Visible = False
End Sub

Cleaner Reusable Version

If you want to keep your code DRY (Don't Repeat Yourself), use a shared helper procedure:

' Helper to update command button visibility based on selected option
Sub UpdateSubmitButtonVisibility()
    With Sheets("New Req")
        ' Show button only if "Yes" is selected
        .CommandButton65.Visible = .OptionButton78.Value
    End With
End Sub

' "Yes" button click event
Sub OptionButton78_Click()
    UpdateSubmitButtonVisibility
End Sub

' "No" button click event
Sub OptionButton79_Click()
    UpdateSubmitButtonVisibility
End Sub

Key Notes to Ensure It Works #

• Confirm Control Types: Make sure your option buttons and command button are ActiveX Controls (added via Developer > Insert > ActiveX Controls). This code won't work for Form Controls.
• Check Control Names: In Developer > Design Mode, select each control and verify its name in the top-left name box (e.g., OptionButton78, CommandButton65). Typos here will break the code.
• Verify Radio Button Grouping: Ensure both option buttons share the same GroupName property (in the Properties window when in Design Mode). This keeps them as mutually exclusive radio buttons—Excel defaults this for controls on the same sheet, but double-check if you've modified it.

内容的提问来源于stack exchange，提问作者Jon Elder