Button to Clear All Checkboxes?

[OnePlus]

Hello,

I am a beginner in Excel and trying to produce a speadsheet-based tool in Excel which can be shared around and used to assist players of an online video game. The game in question is a 'Cops and Robbers' style racing game and players can choose to be a racer or a police officer. The idea has been to produce a spreadsheet which contains all the fines for suspects, and the person playing as police officer can check the boxes on the spreadsheet to calculate the amount for each fine. For example; Speeding = $500 fine and Possession of Drugs = $2500 fine. When the checkboxes next to both of these offenses are ticked, the spreadsheet returns the total fine amount which would be $300.

I have successfully produced the spreadsheet and all calculation features are working as intended. However, i would like to include some kind of button in the spreadsheet which will clear and reset all the ActiveX checkboxes once they have been ticked, returning them all from TRUE to FALSE status with one click. The catch is that the entire button functionality must be contained in a way which can be saved within the spreadsheet, so that when i save the file and share it with the other players, they can download and run the spreadsheet and run it without having to programme the macros on their own installations of Excel. I am not sure whether this is possible, but any assistance would be most appreciated.

Kind Regards!

 

[L. Howard]

Try this code by MVP Domenic, slightly modified for OFF only.

Copy to a standard module.

Howard

Sub CheckBoxes_Off() 'by MVP Domenic

    Dim wks As Worksheet
    Dim oCheckBox As CheckBox
    
    Set wks = Worksheets("Sheet1") 'change the sheet name accordingly
    
    For Each oCheckBox In wks.CheckBoxes
        If Not Intersect(oCheckBox.TopLeftCell, wks.Columns("B")) Is Nothing Then
            If oCheckBox.Value = xlOn Then
                oCheckBox.Value = xlOff
            Else
                '
            End If
        End If

    Next oCheckBox
End Sub

 

[Rick Rothstein]

Assuming your checkboxes are on a sheet named "Sheet1", I think this macro should do what you want..

[table="width: 500"]
[tr]
	[td]Sub TurnAllActiveXCheckBoxesOff()
  Dim Obj As Object
  For Each Obj In Sheets("Sheet1").OLEObjects
    If TypeOf Obj.Object Is MSForms.CheckBox Then Obj.Object.Value = False
  Next
End Sub[/td]
[/tr]
[/table]

 

[OnePlus]

Try this code by MVP Domenic, slightly modified for OFF only.

Copy to a standard module.

Howard

Sub CheckBoxes_Off() 'by MVP Domenic

    Dim wks As Worksheet
    Dim oCheckBox As CheckBox
    
    Set wks = Worksheets("Sheet1") 'change the sheet name accordingly
    
    For Each oCheckBox In wks.CheckBoxes
        If Not Intersect(oCheckBox.TopLeftCell, wks.Columns("B")) Is Nothing Then
            If oCheckBox.Value = xlOn Then
                oCheckBox.Value = xlOff
            Else
                '
            End If
        End If


    Next oCheckBox
End Sub

Excellent. Thanks for the informative response! It turns out i made a slight mistake in the OP, for some unusual reason i said the buttons in my spreadsheet were ActiveX but it turns out they're in fact Form Control buttons. However, i used the code you posted and managed to find something similar which ended up working fine when run straight from the Macro menu. I have pasted the code underneath:

Sub uncheck_forms_checkboxes()
    Dim ws As Worksheet
    Dim xshape As Shape
    For Each ws In ThisWorkbook.Worksheets
        For Each xshape In ws.Shapes
            If xshape.Type = msoFormControl Then
                xshape.ControlFormat.Value = False
            End If
        Next
    Next
End Sub

This script clears the checkboxes no problem, but the issue here is that it doesn't work on Form Control buttons and labels for some reason, showing a message which reads 'Run-time Error 438: Object doesn't support property or method'. However, if i select a Form Control Radio Button or Checkbox then it will work perfectly direct from the spreadsheet. Do you have any idea why the normal buttons and labels do not seem to like this script? Thanks!

 

[Rick Rothstein]

Sub uncheck_forms_checkboxes()
    Dim ws As Worksheet
    Dim xshape As Shape
    For Each ws In ThisWorkbook.Worksheets
        For Each xshape In ws.Shapes
            If xshape.Type = msoFormControl Then
                xshape.ControlFormat.Value = False
            End If
        Next
    Next
End Sub

This script clears the checkboxes no problem, but the issue here is that it doesn't work on Form Control buttons and labels for some reason, showing a message which reads 'Run-time Error 438: Object doesn't support property or method'. However, if i select a Form Control Radio Button or Checkbox then it will work perfectly direct from the spreadsheet. Do you have any idea why the normal buttons and labels do not seem to like this script? Thanks!

First off, it is much easier to clear all of the Form Control Checkboxes (on a single sheet, Sheet1 for this example) than the code you posted (changing CheckBoxes to OptionButtons will do the same for OptionButtons)...

Sub TurnAllFormsCheckBoxesOff()
  Sheets("Sheet1").[B][COLOR="#0000FF"]CheckBoxes[/COLOR][/B].Value = False
End Sub

Now, as to the other part of your question... what exactly are you trying to do with the Form Control buttons and labels? You cannot "turn them off", so what is it you want to do with them?

 

[OnePlus]

First off, it is much easier to clear all of the Form Control Checkboxes (on a single sheet, Sheet1 for this example) than the code you posted (changing CheckBoxes to OptionButtons will do the same for OptionButtons)...

Sub TurnAllFormsCheckBoxesOff()
  Sheets("Sheet1").CheckBoxes.Value = False
End Sub

Now, as to the other part of your question... what exactly are you trying to do with the Form Control buttons and labels? You cannot "turn them off", so what is it you want to do with them?

Hello! Thank you for your responses, i did not notice the first until after posting my response so i do apologise for that. Yes, i see what you mean regarding your suggestion to clear checkboxes from the entire sheet. The code is much more efficient that what i posted earlier, and it serves the same purpose so that is a lot better. I was just trying to find something that worked rather than concentrating on actually what the code meant, but after studying it i can understand now and feel like i have learnt something constructive from this!

I tried importing your code into my spreadsheet as a new macro, and it works very well indeed. I can now attach the macro to the buttons without the error message and clicking the button clears all the checkboxes, exactly as intended. At the moment i do not have much experience in Excel, with the exception of a few sessions at college where i was tutored on basic functionality such as how to use IF and VGET formulas. So i appreciate the assistance, and hopefully i can continue learning new skills in the future! Kind Regards.