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]

First, here's the macro that will reset all option buttons that are on the same row as the button. Right-click the sheet tab for Survey1, select View Code, and then paste the following code in the worksheet's code module...

Option Explicit

Private Sub ResetOptionButtons()

    Dim resetButton As Button
    Set resetButton = Me.Buttons(Application.Caller)
    
    Dim currentOptionButton As OptionButton
    For Each currentOptionButton In Me.OptionButtons
        If currentOptionButton.TopLeftCell.Row = resetButton.TopLeftCell.Row Then
            currentOptionButton.Value = xlOff
        End If
    Next currentOptionButton
    
End Sub

Then assign all buttons, except the one that resets all option buttons, the ResetOptionButtons macro using the following code. In the Visual Basic Editor, insert a regular module (Insert > Module), and then paste the code in the module. Change the name of the button that resets all of the option buttons, where specified in the code.

Option Explicit

Sub AssignMacroToButtons()

    Dim targetWorksheet As Worksheet
    Set targetWorksheet = ActiveWorkbook.Worksheets("Survey1")

    Dim currentButton As Button
    For Each currentButton In targetWorksheet.Buttons
        If currentButton.Name <> "ResetAllButton" Then 'change the name accordingly
            currentButton.OnAction = targetWorksheet.CodeName & ".ResetOptionButtons"
        End If
    Next currentButton
    
End Sub

Hope this helps!

 

[Micron]

Would another approach be to group each row of option buttons (except for the reset button, whose name matches the group name) and loop over the controls (shapes) where the group name matches the reset button name?

 

[DetroitJimBurke]

First, here's the macro that will reset all option buttons that are on the same row as the button. Right-click the sheet tab for Survey1, select View Code, and then paste the following code in the worksheet's code module...

Option Explicit

Private Sub ResetOptionButtons()

    Dim resetButton As Button
    Set resetButton = Me.Buttons(Application.Caller)
   
    Dim currentOptionButton As OptionButton
    For Each currentOptionButton In Me.OptionButtons
        If currentOptionButton.TopLeftCell.Row = resetButton.TopLeftCell.Row Then
            currentOptionButton.Value = xlOff
        End If
    Next currentOptionButton
   
End Sub

Then assign all buttons, except the one that resets all option buttons, the ResetOptionButtons macro using the following code. In the Visual Basic Editor, insert a regular module (Insert > Module), and then paste the code in the module. Change the name of the button that resets all of the option buttons, where specified in the code.

Option Explicit

Sub AssignMacroToButtons()

    Dim targetWorksheet As Worksheet
    Set targetWorksheet = ActiveWorkbook.Worksheets("Survey1")

    Dim currentButton As Button
    For Each currentButton In targetWorksheet.Buttons
        If currentButton.Name <> "ResetAllButton" Then 'change the name accordingly
            currentButton.OnAction = targetWorksheet.CodeName & ".ResetOptionButtons"
        End If
    Next currentButton
   
End Sub

Hope this helps!

Thanks for the reply. I've done a lot of VBA coding in Access, but it's a bit different in Excel and a bit less intuitive as to how all this works together. I realize where these routines are stored, but how are these routines invoked? It's MUCH simpler in Access!

 

[Micron]

I've done a lot of VBA coding in Access, ... It's MUCH simpler in Access!

Ditto!
It's quite similar in a lot of ways though. Excel has its own events and just like Access, sheet event code doesn't have full project scope (at least by default). Standard module code does by default. Event code is invoked pretty much the same way as in Access.

 

[Domenic]

Would another approach be to group each row of option buttons (except for the reset button, whose name matches the group name) and loop over the controls (shapes) where the group name matches the reset button name?

Oh yes, that's definitely another approach, and should be more efficient since you're only looping through the relevant controls. And, it's not reliant on the location of the option button in relation to its corresponding reset button. As you know, it's just a matter of grouping each row of option buttons and renaming them to match their corresponding reset button name (or an assigned alternative text).

 

[DetroitJimBurke]

Would another approach be to group each row of option buttons (except for the reset button, whose name matches the group name) and loop over the controls (shapes) where the group name matches the reset button name?

Oh yes, that's definitely another approach, and should be more efficient since you're only looping through the relevant controls. And, it's not reliant on the location of the option button in relation to its corresponding reset button. As you know, it's just a matter of grouping each row of option buttons and renaming them to match their corresponding reset button name (or an assigned alternative teI can't get this to work. There i sso

Oh yes, that's definitely another approach, and should be more efficient since you're only looping through the relevant controls. And, it's not reliant on the location of the option button in relation to its corresponding reset button. As you know, it's just a matter of grouping each row of option buttons and renaming them to match their corresponding reset button name (or an assigned alternative text).

I can't get that code to work. I'm sure I'm missing something very basic here and just understand the flow of control. When/where does the AssignMacroToButtons sub get called?

 

[Domenic]

AssignMacroToButtons is there only to automatically assign the macro name to each button, except the reset all button. In the code I assumed that the reset all button is called ResetAllButton. Change the name in the code to the actual name of your reset all button. Then you simply manually run that code once, and it will automatically assign the macro called ResetOptionButtons to all of the buttons. Then, when you click on one of your buttons, ResetOptionButtons gets called automatically, and clears the relevant option buttons.

Does this help?

 

[DetroitJimBurke]

AssignMacroToButtons is there only to automatically assign the macro name to each button, except the reset all button. In the code I assumed that the reset all button is called ResetAllButton. Change the name in the code to the actual name of your reset all button. Then you simply manually run that code once, and it will automatically assign the macro called ResetOptionButtons to all of the buttons. Then, when you click on one of your buttons, ResetOptionButtons gets called automatically, and clears the relevant option buttons.

Does this help?

My button is called ResetRow1. In the worksheet I have the following:

Private Sub ResetRow1_Click()

Call ResetOptionButtons

End Sub

Private Sub ResetOptionButtons()

Dim resetButton As Button
Set resetButton = Me.Buttons(Application.Caller)

Dim currentOptionButton As OptionButton
For Each currentOptionButton In Me.OptionButtons
If currentOptionButton.TopLeftCell.Row = resetButton.TopLeftCell.Row Then
currentOptionButton.Value = xlOff
End If
Next currentOptionButton

End Sub

When I click that button it gets to this line of code:
Set resetButton = Me.Buttons(Application.Caller)

And I get runtime error 1004 - Method 'buttons' of object '_Worksheet' failed.

 

[Domenic]

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.