Check boxes

[pincivma]

I found these two macros on the internet. The first macro works fine. I have no problem with that. What I want is also this: When the check box is ticked, I want the cell that the check box is in to turn green. The second macro only works on one check box. It does not work on all of them and I have 466 check boxes to deal with. Please help.

Sub Checkboxes()

i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells(i, "C").Address
i = i + 1
Next cb
End Sub

Sub CheckBox1_Click()
Dim xRng As Range
Set xRng = Selection
If CheckBox1.Value = True Then
xRng.Interior.Color = vbGreen
Else
xRng.Interior.Color = xlNone
End If
End Sub

 

[Domenic]

For your first question, first add the following macro to your module...

Sub CheckBoxFormatting()


    Dim cb As CheckBox
    
    Set cb = ActiveSheet.Checkboxes(Application.Caller) 'Application.Caller returns the name of the checkbox that was checked
    
    If cb.Value = 1 Then
        cb.TopLeftCell.Interior.Color = vbGreen
    Else
        cb.TopLeftCell.Interior.Color = xlNone
    End If


End Sub

Then amend your macro so that it assigns the above macro to each checkbox....

Sub Checkboxes()


    Dim cb As CheckBox
    Dim i As Long
    
    i = 2
    For Each cb In ActiveSheet.Checkboxes
        cb.LinkedCell = Cells(i, "C").Address
        cb.OnAction = "CheckBoxFormatting"
        i = i + 1
    Next cb
    
End Sub

 

[pincivma]

Thanks for the code. You are a Genius. I will dive it a try later on.

 

[pincivma]

I tried your code and it did not work. When the check box was clicked, it did not change color to green

 

[Domenic]

When you ran your Checkboxes() macro, did you make sure that the sheet containing your checkboxes was the active sheet?

 

[pincivma]

Yes.

 

[Domenic]

If you check/select a checkbox, does the linked cell display True?

 

[pincivma]

The linked cells do display True. But I messed up. I had conditional formatting on those cells that had the check boxes. I did not realize that that was causing the problem. When I took out the conditional formatting. The cells did turn green. My fault. SORRY!!

 

[Domenic]

That's great, glad you've sorted it out.

Cheers!