checkbox button color

[Parastoo]

I want to insert many checkbox buttons to many cells and then by checking each one(true) every cell changes his color separately. Actually I need to link each checkbox button to its own cell. I can't do it one by one. Can you help me with this please? Or is it possible to copy / paste a toggle button to use it on the same sheet multiple times, using the same code? It's possible?

 

[NoSparks]

Actually I need to link each checkbox button to its own cell. I can't do it one by one. Can you help me with this please?

You don't say if you're using check boxes from the Form or ActiveX controls.
Posts 2 & 5 from this thread here will show how it can be done in bulk for either.

 

[Parastoo]

You don't say if you're using check boxes from the Form or ActiveX controls.
Posts 2 & 5 from this thread here will show how it can be done in bulk for either.

I used Form control checkbox. I create another column for each day of month for linking each cell and then hide that column. It's silly and time-consuming.

 

[NoSparks]

If this were my project, and it isn't, I'd link the check box to the cell it's on and format those cells so the true or false won't show, and I'd do the whole month at once with this code in a standard module. Any click of any check box will run the 'RunForAllChkBoxes' macro.

Sub Insert_Form_Checkboxes()
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox

With ActiveSheet
    'delete ALL existing checkboxes from sheet, links are NOT cleared.
    .CheckBoxes.Delete  '<~~~~~ comment out to NOT delete existing checkboxes
    'Exit Sub           '<~~~~~ uncomment when deleting checkboxes only.
    Set myRng = .Range("o3:as15")    '<~~~~~ enter the range to have checkboxes
End With
Application.ScreenUpdating = False
    For Each myCell In myRng.Cells
        With myCell
            Set CBX = .Parent.CheckBoxes.Add _
                        (Top:=.Top, Left:=.Left, _
                         Width:=.Width, Height:=.Height) 'click area same size as cell
            
            CBX.Name = "CBX_" & .Address(0, 0)      'name includes address of checkbox
            CBX.Caption = ""                        'whatever you want, "" for none
            CBX.Value = xlOff                       'initial value unchecked
            CBX.LinkedCell = .Offset(0, 0).Address  'linked to the cell its in
            CBX.OnAction = "RunForAllChkBoxes"      'run this each time clicked
            'format the cell
            .NumberFormat = ";;;"                   'make cell contents not show
            .Interior.ColorIndex = 56               'color black to start
        End With
    Next myCell
Application.ScreenUpdating = True
End Sub

Sub RunForAllChkBoxes()
    Dim fromWhere As String
    
    With ActiveSheet.CheckBoxes(Application.Caller)
        fromWhere = Split(Application.Caller, "_")(1)
        If .Value = xlOff Then
            Range(fromWhere).Interior.ColorIndex = 56
        Else
            Range(fromWhere).Interior.ColorIndex = 4
        End If
    End With
End Sub

 

[Parastoo]

Thank You very much. I'll try it.