Macro to link checkboxes to cells

ClaireD

New Member
Joined
Nov 29, 2016
Messages
25
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Claire,

Please describe more clearly how you want the checkboxes and cells "linked". Are you referring to:

1. A checkbox can be linked to a cell so that when the checkbox is clicked a True or False value is toggled in that cell. This works in both directions- so changing the True/False value in the Cell can change the checkbox state.

2. Having all your checkboxes call the same macro and passing the range location of the clicked checkbox to the macro so it can process the code based on which box was checked.

3. or something else??
 
Upvote 0

ClaireD

New Member
Joined
Nov 29, 2016
Messages
25
Option 1 is what I'm after, however there are too many tickboxes to do manually. The number of tickboxes will vary and the users aren't very excel savvy.
 
Upvote 0

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Are the users going to need to add or remove checkboxes, or will that only be done by someone more Excel-savvy?
 
Upvote 0

ClaireD

New Member
Joined
Nov 29, 2016
Messages
25
The users need to add and remove checkboxes but can't manually link checkboxes to cells.
 
Upvote 0

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Claire, I've been offline for the holidays. Sorry for my delay in getting back to you.

When users add checkboxes, there will need to be some additional action to trigger linking the checkboxes to adjacent cells. One option would be to have a button that they click that runs a macro that sets (or resets) the linked cell for each checkbox to a cell with a specified relationship to the checkbox.

There's two types of checkbox controls- ActiveX and Form controls. Which type are you using? If you aren't sure, Right-Click on one of the Checkboxes. If one of the Right-Click menu options is "Assign Macro...", then it is a Form control.
 
Upvote 0

ClaireD

New Member
Joined
Nov 29, 2016
Messages
25
Hi Jerry,

I'm using form controls and this sounds like a good option! The control would need to select all tick boxes except any in row 11 (these are select all triggers).

Thanks,

Claire
 
Upvote 0

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Claire, Below is a macro for you to try. Add a button to your worksheet and assign the button to run the Sub LinkCheckBoxesToAdjacentCells.

Code:
Sub LinkCheckBoxesToAdjacentCells()
'--steps through each Form CheckBox on the ActiveSheet
'    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
 
 For Each cbx In ActiveSheet.CheckBoxes
   dTop = cbx.Top + 0.5 * cbx.Height
   Set rCellWithCheckBox = rGetAddressAtCoordinates(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 = IIf(rCellWithCheckBox.Column > 1, -1, 1)
      cbx.LinkedCell = rCellWithCheckBox.Offset(0, lOffset).Address
   End If
 Next cbx

End Sub

Function rGetAddressAtCoordinates(dLeft As Double, dTop As Double) As Range
 With ActiveSheet.Shapes.AddLine(dLeft, dTop, dLeft, dTop)
   Set rGetAddressAtCoordinates = .TopLeftCell
   .Delete
 End With
End Function
 
Upvote 0

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Is it possible to specify the sheet as "Broadcaster Match Selections"

Yes. Replace the previous code with this...

Code:
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 = IIf(rCellWithCheckBox.Column > 1, -1, 1)
      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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,191,069
Messages
5,984,451
Members
439,888
Latest member
c3rberus

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