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]

By the way, make sure that each reset button is located within the same row of its corresponding option buttons. Otherwise, if the top left corner of the button is located slightly higher than the option buttons, and is actually located in the row above, then things work out.

 

[Domenic]

I'm not sure why you've included ResetRow1_Click(). It's not required as part of my solution.

You only need to run AssignMacroToButtons () once to assign ResetOptionButtons() to each of your buttons. Then, once you click on any of the buttons, ResetOptionButtons() will be called and its corresponding option buttons will be cleared.

 

[Micron]

So am I to understand that if the top value of any reset button isn't exactly the same as the other controls in a row that that code will fail?
That would seem quite easy to mess up when it gets working. If anyone resizes rows in the sheet the code could fail then too?

 

[Domenic]

So am I to understand that if the top value of any reset button isn't exactly the same as the other controls in a row that that code will fail?
That would seem quite easy to mess up when it gets working. If anyone resizes rows in the sheet the code could fail then too?

Yes, very true. Your earlier suggestion would be best.

 

[Domenic]

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

 

[DetroitJimBurke]

As I mentioned in my original post, ResetOptionButtons() needs to be placed in the code module for your sheet, which I see from your original post is called "Survey1". So you'll need to right-click the sheet tab, then you'll need to select View Code, and then you'll need to post the code in the code module.

That is where my code is, I triple checked. I appreciate all your help, this is incredibly frustrating. I've wasted a lot of time trying to figure this out, and I know it's something that should take five minutes.

Believe or not I have years of experience programming a fairly complicated Access application and could do all of this in an instant with Access. There's something I just don't understand about how Excel handles all this. I know this is typically a very simple thing to do, and there is some basic concept I'm just not getting. I've dealt with referencing 'standard' cell values in VBA code, moving from row to row, deleting and inserting rows, changing cell values, etc. There's something different about dealing with controls as opposed to cell values that I am missing, and I'm sure it's fairly basic to any Excel 'pro'. Also, I used Form controls as opposed to ActiveX controls in my spreadsheet, and I see that they are handled differently, maybe that's part of the problem. In the meantime I ordered an Excel Power programming book and am waiting for it to be delivered. Can't find any of that at Barnes and Noble any more, just really basic stuff.

 

[DetroitJimBurke]

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

Ah, you typed this is while I was replying to the previous message. I'll read through this....

 

[Domenic]

Ah, you typed this is while I was replying to the previous message. I'll read through this....

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.

 

[Micron]

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.

 

[Micron]

This approach is better, you don't have to worry about the locations of the various controls, and should be more efficient.

By now you should be able to tell that I was not really capable of implementing the idea without a lot of trial and error and surely, a lesser result. I bet I spent an hour researching object model and came up with code to get closer to seeing the model relationships and dependencies never mind fashioning something from it.