Clear all Option/Radio Buttons on a sheet

[cdterford]

Hi all, this is my first post on the forum after spending weeks browsing for help on other topics. Today though, I need some help with VBA code. Im very new to it, and so far am really only able to copy and paste proper code; I don't know the visual basic coding language at all and I definitely cant write my own as of yet.
Anyway, the help I need seems like it should be so simple to me, but after posting on http://stackoverflow.com/questions/31060076/form-option-button-reset-macro, I still was unable to get the correct answer I needed.

I need a VBA code that can be used by a button. This code should set all of the option buttons on the sheet to blank, or false (as I understand it, false = unchecked).

I am using form control buttons; not activex buttons. Is this wrong -- should i be using activex buttons for a sheet? I'm trying to be as clear as I can so that help will be easy to give. I have a sheet with text and boxes that is going to be used as an industrial parts order form. It has different sections, each with many options. In some of these sections, only one option may be selected. Previously, I had used checkboxes to good effect, allowing a user to check and uncheck anything as desired, however it was brought to my attention that I should make this sheet in such a way that only one option may be selected per section; checkboxes allow multiple options to be selected per section. Option buttons solve this problem, but once checked, one per group must remain selected, and I would like an option to clear all selections.

Any help is so greatly appreciated.

 

[wellsr]

The macro below should do the trick for you. It builds on the stackoverflow response, and hopefully resolves the errors you were receiving.

Sub Reset()
    For Each vctrl In ActiveSheet.Shapes
        If vctrl.Type = msoFormControl Then
            If vctrl.FormControlType = xlOptionButton Then
                vctrl.DrawingObject.Value = False
            End If
        End If
    Next
End Sub

 

[Rick Rothstein]

If you are using Forms Option Buttons, then this single line of code should do what you want (put it in whatever procedure you want or create a macro specially for it)...

ActiveSheet.OptionButtons = False

If you have a specific sheet you want to direct the code line to, just replace the ActiveSheet reference with a reference to that sheet. For example, if the option buttons are on Sheet1...

Sheets("Sheet1").OptionButtons = False

 

[wellsr]

Well, don't I feel dumb. My solution resolved the run time error '438' you encountered on the SO post, but clearly I took the long approach. Good answer Rick.

 

[cdterford]

Well, don't I feel dumb.

...this single line of code should do what you want.

Thanks so much! Both of these work for me. Much appreciated!

 

[colin5392]

Is there a way of assigning that to something that does not require changing the spreadsheet to a macro?

[h=1]Sheets("Sheet1").OptionButtons = False[/h]