checkbox button color

Parastoo

New Member
Joined
Sep 10, 2023
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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.
1694409808568.png


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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 1
Solution

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top