Reset option (radio) buttons only for one row

[DetroitJimBurke]

I have a macro to reset all of my option buttons on a sheet using

Sheets("Survey1").OptionButtons = False

This works great, but I also need to be able to reset option buttons on only a selected row. I'll have a 'reset' button on each row that will reset the buttons for that row. Thanks!

 

[Domenic]

@Micron

Like anything, the more time one spends writing code, the better one gets.

As to why learn it? Well, while it can be frustrating at times, it can also be challenging and rewarding in its own way.

Cheers!

 

[DetroitJimBurke]

Boy, can I relate to that rant! I find the Excel object model baffling and some of it just doesn't make sense. Surely Access and Excel design groups never got together in the same room, or if the did it wasn't for business. On one hand, I'm here to learn it by helping out. On the other, I often wonder why because I'm retired and no one is going to hire me for this. I guess it's better than playing "mind development" games on my phone.

I just came out of retirement to work with a friend on his business. My guess is that Excel was originally created simply to use the spreadsheet cells, and then over time the control functionality was added on without too much concern for 'simplicity'. It seems like they could have made a simpler (and more obvious) way to refer to values in VBA, that's for sure, similar to how you refer to cell values (which really is simple). After working with Access for many years, the methodology of building and editing controls and manipulating them through VBA seems a bit 'convoluted'. I guess once you know the tricks it becomes 'simple', but it's learning those tricks that is, well, tricky. OK, ranting done, on to see if I can get this all working! I much appreciate any and all help, and in being allowed to vent a bit.

 

[DetroitJimBurke]

Good, and you'll notice that I've included a sample workbook. This approach is better, you don't have to worry about the locations of the various controls, and should be more efficient. Let me know if you need further help.

I really appreciate you taking the time to do all of this. Unfortunately, I'm still getting an error, when this line of code is executed:

Set resetButton = Me.Shapes(Application.Caller)

When I hover over Application.Caller it shows error 2023.

This is the code I have. ResetRow1 is the command button that is being used to reset the option buttons. The code is in the Module for the worksheet that has the buttons

Private Sub ResetRow1_Click()

Dim resetButton As Shape
Set resetButton = Me.Shapes(Application.Caller)

Dim currentShape As Shape
For Each currentShape In Me.Shapes(resetButton.AlternativeText).GroupItems
currentShape.ControlFormat.Value = xlOff
Next currentShape

End Sub

 

[DetroitJimBurke]

@Micron

Like anything, the more time one spends writing code, the better one gets.

As to why learn it? Well, while it can be frustrating at times, it can also be challenging and rewarding in its own way.

Cheers!

Okay, here's the other approach, as described by @Micron.

First, for each row of option buttons, group them, and give them a name. So, for example, select the first row of options buttons, then Ribbon >> Shape Format tab >> Arrange group >> Group button >> Group. Then, in the Name box, change the name of the group to Group 1. And do the same for your other buttons.

Then, for each button, set the Alternative Text to its corresponding group name. So, for example, for the first button you would set it to Group 1, and so on.

Then, copy and paste the following code into the code module for your sheet. So right-click the sheet tab, select View Code, and then paste the code into the module.

Private Sub ResetOptionButtons()

    Dim resetButton As Shape
    Set resetButton = Me.Shapes(Application.Caller)

    Dim currentShape As Shape
    For Each currentShape In Me.Shapes(resetButton.AlternativeText).GroupItems
        currentShape.ControlFormat.Value = xlOff
    Next currentShape
 
End Sub

Here's a sample workbook, which you can download. Note that it will be available for a few days.

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

1drv.ms

Success! Wow, that is the longest I've spent on anything in my entire programming life. I 'skipped over' (oops) the part about grouping them and didn't go back and read that part until I'd wasted a bunch more time. Then I didn't realize that Excel puts a space in the control names it creates, so it had 'Group 4' rather than 'Group4' with no space. So it took me a while to figure that out. Whew. I really appreciate you both taking the time to help me out here, you were both really helpful. I've certainly learned a bit about how Excel operates at least. Whew. Glad to have that painful process over with. Thanks again.

 

[Domenic]

I'm glad to hear that you have all that sorted out.

I do apologize, though, I should have offered this last solution in the first place.

Cheers!

 

[DetroitJimBurke]

I'm glad to hear that you have all that sorted out.

I do apologize, though, I should have offered this last solution in the first place.

Cheers!

The problems were due to my ineptitude with this type of Excel programming (which have lessened ever so slightly), including not reading all of the instructions and trying to rush through it too quickly rather than take my time and be very careful with every step. No apologies needed.

 

[Domenic]

No problem, Cheers!