VBA code to reset check boxes within a range

[JWD210]

Hello. I have a range of check boxes in K29:K39 that I would like to have a label reset to unchecked when clicked and cannot figure this out. Does anyone have the proper code for this ?

I tried this but it is not working at all..

Private Sub Label1_Click()
Dim Checkbox As Excel.Checkbox
For Each Checkbox In ActiveSheet.CheckBoxes
If Not Intersect(Range("G29:G39"), Checkbox.TopLeftCell) Is Nothing Then
Checkbox.Value = False
End If
Next Checkbox
End Sub

 

[Micron]

When posting a problem it might help to
- specify which style of control you used Form, or ActiveX as I think it makes a difference
- state what the result or behaviour is. You could mean there's an error, unexpected results, or no results at all
- use code tags (please - vba button on posting toolbar) to maintain indentation and promote readability.

What you posted worked for me with a form checkbox on a sheet but not ActiveX checkbox. What you might do is put a break point in your code, run it and when it stops type ?activesheet.checkboxes.count in the immediate window and hit Enter. See if the count is what you expect. I get 1 where there is one form control and one activex control, whereas I would expect a count of 2. Using ActiveSheet instead of the sheet name might also be an issue if that code runs and the active sheet has no checkboxes. Lastly, I'd never knowingly use the name of an object type or property or method as a variable name. You can cause issues by way of the code not knowing if you're referring to the object itself, a property or method of the object, or your variable. I'm referring to the variable named "Checkbox".

 

[DanteAmor]

Hi and welcome to MrExcel.

First, what type of checkbox do you have, the userform control or the ActiveX control?

Second, in which column do you have the checkboxes?
The top left part of the checkbox must be inside the column cell. For example, if they are in column G:

If the checkboxes are UserForm Control, then use the following code:

Private Sub Label1_Click()
  Dim Chbox As Checkbox
  For Each Chbox In CheckBoxes
    If Not Intersect(Range("G29:G39"), Chbox.TopLeftCell) Is Nothing Then
      Chbox.Value = False
    End If
  Next Chbox
End Sub

If the checkboxes are ActiveX Control, then use the following code:

Private Sub Label1_Click()
  Dim obj As OLEObject
 
  For Each obj In ActiveSheet.OLEObjects
    If TypeName(obj.Object) = "CheckBox" Then
      If Not Intersect(Range("G29:G39"), obj.TopLeftCell) Is Nothing Then
        obj.Object.Value = False
      End If
    End If
  Next
End Sub

If the corners of the checkboxes are in another column, for example K, then change the range in the code:

Range("K29:K39")​

 

[JWD210]

Hi and welcome to MrExcel.

First, what type of checkbox do you have, the userform control or the ActiveX control?

Second, in which column do you have the checkboxes?
The top left part of the checkbox must be inside the column cell. For example, if they are in column G:

View attachment 120310​

If the checkboxes are UserForm Control, then use the following code:

Private Sub Label1_Click()
  Dim Chbox As Checkbox
  For Each Chbox In CheckBoxes
    If Not Intersect(Range("G29:G39"), Chbox.TopLeftCell) Is Nothing Then
      Chbox.Value = False
    End If
  Next Chbox
End Sub

If the checkboxes are ActiveX Control, then use the following code:

Private Sub Label1_Click()
  Dim obj As OLEObject
 
  For Each obj In ActiveSheet.OLEObjects
    If TypeName(obj.Object) = "CheckBox" Then
      If Not Intersect(Range("G29:G39"), obj.TopLeftCell) Is Nothing Then
        obj.Object.Value = False
      End If
    End If
  Next
End Sub

If the corners of the checkboxes are in another column, for example K, then change the range in the code:

Range("K29:K39")​

Thank you, I'm not sure what kind of checkbox it was... I used the one off the Insert -> Checkbox from the ribbon, not from the developer tab. Guess that's where the main problem was.

And the ones from the developer tab work great with the code you provided. What type of checkbox is the one that isn't working? (actually like how this style of checkbox looks over the other two)

 

[JWD210]

When posting a problem it might help to
- specify which style of control you used Form, or ActiveX as I think it makes a difference
- state what the result or behaviour is. You could mean there's an error, unexpected results, or no results at all
- use code tags (please - vba button on posting toolbar) to maintain indentation and promote readability.

What you posted worked for me with a form checkbox on a sheet but not ActiveX checkbox. What you might do is put a break point in your code, run it and when it stops type ?activesheet.checkboxes.count in the immediate window and hit Enter. See if the count is what you expect. I get 1 where there is one form control and one activex control, whereas I would expect a count of 2. Using ActiveSheet instead of the sheet name might also be an issue if that code runs and the active sheet has no checkboxes. Lastly, I'd never knowingly use the name of an object type or property or method as a variable name. You can cause issues by way of the code not knowing if you're referring to the object itself, a property or method of the object, or your variable. I'm referring to the variable named "Checkbox".

I'll try to be more specific in the future and use the code tags - thank you! I'm not sure what type of checkbox I chose, but it appears to have been the wrong one, hence why there was nothing happening when I clicked the label to reset it.

 

[DanteAmor]

What type of checkbox is the one that isn't working?

It's a checkbox for the 365 version and I don't know the code to control it.

Maybe someone else can help you with the code, otherwise you will have to use one of the other two, I recommend the activex control.

 

[Alex Blakenburg]

I am not really sure what the relationship is between column K in your description and column G in your code but see if this gives you some ideas:

Private Sub Label1_Click()
    Dim rng As Range, rCell As Range
    Set rng = Range("G29:G39")
    
    For Each rCell In rng
        If rCell.CellControl.Type = xlTypeCheckbox Then
            rCell.Value = False
        End If
    Next rCell
End Sub