How do I create a reset button that unchecks checkboxes in my spreadsheet - but not all of them?

[Alphaba1]

I have a spreadsheet with numerous checkboxes in Row A. I have created a reset button to uncheck the buttons, however I can only find a code for macros that will reset ALL the checkboxes. I do not want the checkbox in cell A26 to reset when the button is pressed. Is there a way to exclude specific checkboxes? I am a complete newbie, so apologies if the answer is an obvious one. Thankyou.
Below is the code I have:

Sub ResetDataClearCB()
For Each cb In Worksheets("COMMERCIAL").checkboxes
cb.Value = x1Off
Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you set 'Require Variable Declaration' in the VBA window: Tools -> Options -> Editor
I suspect that in your code you did not mean to have the digit "1" in x1Off but rather meant a lower case "L" xlOff
Having the setting I mentioned would catch things like that.

checkboxes in Row A.

Further, it will help avoid confusion if you use the correct terminology. Where you have your checkboxes is Column A.


Assuming they are Form Controls checkboxes, try like this

Sub ResetDataClearCB_v2()
  Dim cb As CheckBox
 
  For Each cb In Worksheets("COMMERCIAL").CheckBoxes
    If cb.Caption <> "Fuel Surcharge" Then cb.Value = xlOff
  Next
End Sub

 

[dmt32]

Hi welcome to formum

If it is just the one checkbox you want the code to ignore then the addition of a simple If statement should do what you want

Sub ResetDataClearCB()
    Dim cb As CheckBox
    For Each cb In Worksheets("COMMERCIAL").CheckBoxes
      If cb.TopLeftCell.Address(0, 0) <> "A26" Then cb.Value = x1Off
    Next
End Sub

However, if you need to include multiple checkboxes then you may find Select Case statement a better way to go

Sub ResetDataClearCB()
    Dim cb As CheckBox
    For Each cb In Worksheets("COMMERCIAL").CheckBoxes
     Select Case cb.TopLeftCell.Address(0, 0)
     Case "A26", "A17", "A10"
      'do nothing
     Case Else
        cb.Value = x1Off
    End Select
    Next
End Sub

Dave

 

[Alphaba1]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you set 'Require Variable Declaration' in the VBA window: Tools -> Options -> Editor
I suspect that in your code you did not mean to have the digit "1" in x1Off but rather meant a lower case "L" xlOff
Having the setting I mentioned would catch things like that.



Further, it will help avoid confusion if you use the correct terminology. Where you have your checkboxes is Column A.


Assuming they are Form Controls checkboxes, try like this

Sub ResetDataClearCB_v2()
  Dim cb As CheckBox
 
  For Each cb In Worksheets("COMMERCIAL").CheckBoxes
    If cb.Caption <> "Fuel Surcharge" Then cb.Value = xlOff
  Next
End Sub

Thankyou so much for your help and advice. I do apologize for my mistakes. I'm learning a lot. That looks like its done the trick

 

[Alphaba1]

Hi welcome to formum

If it is just the one checkbox you want the code to ignore then the addition of a simple If statement should do what you want

Sub ResetDataClearCB()
    Dim cb As CheckBox
    For Each cb In Worksheets("COMMERCIAL").CheckBoxes
      If cb.TopLeftCell.Address(0, 0) <> "A26" Then cb.Value = x1Off
    Next
End Sub

However, if you need to include multiple checkboxes then you may find Select Case statement a better way to go

Sub ResetDataClearCB()
    Dim cb As CheckBox
    For Each cb In Worksheets("COMMERCIAL").CheckBoxes
     Select Case cb.TopLeftCell.Address(0, 0)
     Case "A26", "A17", "A10"
      'do nothing
     Case Else
        cb.Value = x1Off
    End Select
    Next
End Sub

Dave

Hi Dave, that's perfect! Thanks so much. I do actually need to include multiple checkboxes, so this is great. Thanks again for your help.

 

[Peter_SSs]

I do actually need to include multiple checkboxes

A similar approach can be taken with the CheckBox captions so it is probably a matter of deciding what is most reliable for your circumstances.
Using .TopLeftCell.Address could possibly be an problem if rows are added or deleted.
Using the CheckBox caption could be a problem if the captions might be changed.

Anyway, if going with Captions, it would be something like this

Sub ResetDataClearCB_v3()
  Dim cb As CheckBox
  
  For Each cb In Worksheets("COMMERCIAL").CheckBoxes
    Select Case cb.Caption
      Case "Fuel Surcharge", "WA Regional Surcharge"  '<- Add more if required
      Case Else:  cb.Value = xlOff
    End Select
  Next cb
End Sub

 

[dmt32]

Hi Dave, that's perfect! Thanks so much. I do actually need to include multiple checkboxes, so this is great. Thanks again for your help.

You are welcome & glad we were able to help & appreciate your feedback.
Please remember to mark the solution you used.

Dave