Macro to link checkboxes to cells

[ClaireD]

Hi there,

I want to link checkboxes to the cells that they are in using a macro for all cells in the range =H14:AK143 in a sheet called Broadcaster Match Selection. I only want this to apply on this tab in this range.

The number and position of these checkboxes will vary so it needs to update every time checkboxes are added or edited.

Any help appreciated!

Thanks,

Claire

 

[ClaireD]

Sorry for the delay in getting back to you. This works almost perfectly with one problem.

It is linking the checkbox to the wrong cell. So for example the tickbox in cell H14 is linking with cell G14. So all of them are linking to the cell to the left. Can you help?

 

[Jerry Sullivan]

I assumed you wanted to link to the adjacent cell. If you link to the same cell: 1. The "TRUE/FALSE" might overlap the caption or checkbox, 2. It's harder for users to copy-paste TRUE/FALSE, values without also copying the checkboxes.

If you want to do that though, just replace this line:

     lOffset = IIf(rCellWithCheckBox.Column > 1, -1, 1)

with this:

     lOffset = 0

 

[ClaireD]

Works a treat. Thanks very much!

 

[ClaireD]

Hi,

This has been working brilliantly but just wondering if you assist me with a macro that unlinks all of the checkboxes from the cells?

The final macro code for linking them was:

Sub LinkCheckBoxesToAdjacentCells()'--steps through each Form CheckBox on the specified sheet.
' and sets or resets the CheckBox' LinkedCell to the
' adjacent cell to the left of the cell containing the CheckBox.


Dim cbx As CheckBox
Dim dTop As Double
Dim lOffset As Long
Dim rCellWithCheckBox As Range
Dim wks As Worksheet

Set wks = Sheets("Broadcaster Match Selections")

For Each cbx In wks.CheckBoxes
dTop = cbx.Top + 0.5 * cbx.Height
Set rCellWithCheckBox = rGetAddressAtCoordinates( _
wks:=wks, dLeft:=cbx.Left, dTop:=dTop)
'--do nothing if checkbox is on Row 11.
If rCellWithCheckBox.Row <> 11 Then
'--link to cell to the left unless checkbox in column A, then link to the right.
lOffset = 0
cbx.LinkedCell = rCellWithCheckBox.Offset(0, lOffset).Address
End If
Next cbx


End Sub


Function rGetAddressAtCoordinates(wks As Worksheet, dLeft As Double, _
dTop As Double) As Range

With wks.Shapes.AddLine(dLeft, dTop, dLeft, dTop)
Set rGetAddressAtCoordinates = .TopLeftCell
.Delete
End With
End Function

 

[Jerry Sullivan]

...just wondering if you assist me with a macro that unlinks all of the checkboxes from the cells?

Hi Claire,

If you are just wanting to clear the RowSource property of all checkboxes (so there are no links), you could use this....

Sub UnLinkCheckBoxesFromCells()
 '--steps through each Form CheckBox on the specified sheet.
 '  and clears the CheckBox' LinkedCell property
 Dim cbx As CheckBox
 Dim wks As Worksheet

 Set wks = Sheets("Broadcaster Match Selections")

 For Each cbx In wks.CheckBoxes
   '--unlink the Checkbox
   cbx.LinkedCell = vbNullString
 Next cbx

End Sub

I've assumed you don't need any special handling of Row 11, since those checkboxes weren't linked by the original code. Let me know if that isn't the case.

btw, Since your other code is no longer linking to adjacent cells (as I misunderstood your intent), you could remove the parts about offsetting to adjacent cells...

Sub LinkCheckBoxesToTheirCells()
 '--steps through each Form CheckBox on the specified sheet.
 '  and sets or resets the CheckBox' LinkedCell to the
 '  same cell containing the CheckBox.

 Dim cbx As CheckBox
 Dim dTop As Double
 Dim rCellWithCheckBox As Range
 Dim wks As Worksheet

 Set wks = Sheets("Broadcaster Match Selections")

 For Each cbx In wks.CheckBoxes
   dTop = cbx.Top + 0.5 * cbx.Height
   Set rCellWithCheckBox = rGetAddressAtCoordinates( _
      wks:=wks, dLeft:=cbx.Left, dTop:=dTop)
      
   '--do nothing if checkbox is on Row 11.
   If rCellWithCheckBox.Row <> 11 Then
   '--link to cell the cell containing the Checkbox
      cbx.LinkedCell = rCellWithCheckBox.Address
   End If

 Next cbx

End Sub

 

[ClaireD]

Thanks Jerry! I will test this on Monday and let you know how it went.

Just to be clear. What does this bit of code do?

btw, Since your other code is no longer linking to adjacent cells (as I misunderstood your intent), you could remove the parts about offsetting to adjacent cells...

 

[peterghost]

Hi! I would like your help in a little problem that i have. I use this code to add checkboxes to a selected range of cells in excel and link them automatically with each cell (each checkbox is created in the center of its cell). However, if
the width or height of the cells is modified the checkboxes aren't autoadjusted to the new center of the cells. What do i need to add/change?

CODE:
Public Sub Add_ActiveX_Checkboxes()

Dim wks As Worksheet
Dim cell As Range, checkboxCells As Range
Dim objOLE As OLEObject

Set wks = ActiveSheet

Set checkboxCells = Application.Selection
Set checkboxCells = Application.InputBox("Range", "Analysistabs", checkboxCells.Address, Type:=8)

For Each cell In checkboxCells

Set objOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1")

With objOLE
.Width = 12
.Height = 12
.Left = cell.Left + (cell.Width / 2) - (objOLE.Width / 2)
.Top = cell.Top + (cell.Height / 2) - (objOLE.Height / 2)
.Name = "Checkbox_" & cell.Address
.LinkedCell = cell.Worksheet.Name & "!" & cell.Address
'.Placement = xlMove
.Placement = xlMoveAndSize
.Object.Value = False
.Object.BackStyle = fmBackStyleTransparent
'.Object.BackStyle = fmBackStyleOpaque
.Object.TripleState = False 'True
.Object.Caption = ""

End With

Next

End Sub