Question about checkboxes and their cell links

zothox

New Member
Joined
Oct 28, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi, I want to create over 500 checkboxes in a document and I want them to have their own individual cell link so I can use them later for analysis purposes. Is it possible to do this without manually editing every single cell separately? I would like to be able to copy a cell and checkbox but I want the cell link to automatically change to the new cell. Is this possible in excel?
Thanks in advance.

/Zothox
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ouvay

Board Regular
Joined
Jun 9, 2022
Messages
131
Office Version
  1. 2019
Platform
  1. Windows
I found this online for checkboxes...

Make sure you haven't got any checkbox objects in your object list before doing this.. else you'll see redundancies
press alt+f10 and make sure you delete old (currently inactive) checkboxes

Go ahead and create a checkbox... drag down the cell its in to the 500th row
then run this VBA code to assign those checkboxes to the desired range

VBA Code:
Sub LinkChecks()
    Dim xCB
    Dim xCChar
    i = 2
    xCChar = "A" ' this is what you'll need to adjust to assign checkboxes to the desired range
    For Each xCB In ActiveSheet.CheckBoxes
        If xCB.Value = 1 Then
            Cells(i, xCChar).Value = True
        Else
            Cells(i, xCChar).Value = False
        End If
        xCB.LinkedCell = Cells(i, xCChar).Address
        i = i + 1
    Next xCB
End Sub

Hope this helps
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OMG, 500 checkboxes???
I would suggest a different approach, ie using a special character for emulating a checkbox; for example (this is a demo):
-select 3 free cells
-run the following code:
VBA Code:
Sub MakeCB()
    Selection.Value = Chr(111)
    Selection.Font.Name = "Wingdings"
    Selection.Interior.Color = RGB(255, 255, 150)
End Sub
At this point the selected cells will look like a checkbox

-rightclick on the tab with the name of the worksheet, and select View Code to open the vba editor at "the right position"
-copy this code into the empty code frame
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Font.Name = "Wingdings" And Len(Target.Value & " ") = 2 Then
    On Error Resume Next
    If Asc(Target.Value) = 254 Then
        Target.Value = Chr(111)
    ElseIf Asc(Target.Value) = 111 Then
        Target.Value = Chr(254)
    End If
    On Error GoTo 0
End If
Cancel = True
End Sub
Now return to the sheet and double click on any of the "checkboxes": it will flip betwen the checked /unchecked condition

If you copy & past a "checkbox" the behaviour will also be copied

The "linked cell" is the cell itself; it contains a special character whose Asc value is 254 if "checked" or 111 for "unchecked"

If you like the approach, just try...

(this in addition to what already suggested by @ouvay above)
 
Upvote 0

Marius44

New Member
Joined
Aug 30, 2016
Messages
48
Hello
I believe you want to add checkboxes as well.
I figured there is a Checkbox (Non ActiveX Form) in your sheet.
Try this macro (I limited the example to adding only 3 checkboxes).
VBA Code:
Sub Try_Module()
For i = 2 To 4 'only three checkboxes
  ActiveSheet.Shapes.Range(Array("Check Box 1")).Select
  Selection.Copy
  Range("J" & i).Select ' Dynamic cell on wich I place the CheckBox
  ActiveSheet.Paste
  'I need the name of the checkbox added
  x = ActiveSheet.Shapes.Count
  For N = 1 To x
    If ActiveSheet.Shapes(N).Type <> 12 Then
        newcbx = ActiveSheet.Shapes(N).Name
    End If
  Next N
  ActiveSheet.Shapes.Range(Array(newcbx)).Select
  With Selection
    .Value = xlOff
    .LinkedCell = "G" & i 'Dynamic cell on linked column
    .Display3DShading = False
  End With
Next i
End Sub

Let them know.
Hello,
Mario
 
Upvote 0

ouvay

Board Regular
Joined
Jun 9, 2022
Messages
131
Office Version
  1. 2019
Platform
  1. Windows
OMG, 500 checkboxes???
I would suggest a different approach, ie using a special character for emulating a checkbox; for example (this is a demo):
-select 3 free cells
-run the following code:
VBA Code:
Sub MakeCB()
    Selection.Value = Chr(111)
    Selection.Font.Name = "Wingdings"
    Selection.Interior.Color = RGB(255, 255, 150)
End Sub
At this point the selected cells will look like a checkbox

-rightclick on the tab with the name of the worksheet, and select View Code to open the vba editor at "the right position"
-copy this code into the empty code frame
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Font.Name = "Wingdings" And Len(Target.Value & " ") = 2 Then
    On Error Resume Next
    If Asc(Target.Value) = 254 Then
        Target.Value = Chr(111)
    ElseIf Asc(Target.Value) = 111 Then
        Target.Value = Chr(254)
    End If
    On Error GoTo 0
End If
Cancel = True
End Sub
Now return to the sheet and double click on any of the "checkboxes": it will flip betwen the checked /unchecked condition

If you copy & past a "checkbox" the behaviour will also be copied

The "linked cell" is the cell itself; it contains a special character whose Asc value is 254 if "checked" or 111 for "unchecked"

If you like the approach, just try...

(this in addition to what already suggested by @ouvay above)
Very elegant solution! I'm going to save it later.. I work with very large pools of data... often well over 1 million rows and I cannot work with checkboxes.. but this is a very clever workaround
 
Upvote 0

Forum statistics

Threads
1,186,440
Messages
5,957,843
Members
438,325
Latest member
fanofstuff

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
Top