Untick Boxes Macro

[ClaireD]

Hi there,

I have a macro that works perfectly, which ticks all boxes on a particular tab within a certain range. What I need is a macro that undoes this. So the check boxes are no longer assigned to any cell. I've pasted the macro code I have currently which ticks all of the boxes I need.

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

 

[bugmonsta]

I think a quick way of doing it would be like this (or you can loop through - I think I have a bit of code for that somewhere if you want me to find it?)

IN my quick test of 2 whole checkboxes on Sheet1, this will clear them:

Sub test()
 ActiveSheet.CheckBoxes.Value = xlOff
End Sub

*EDIT* Found the loop through code in a file, neither of these are anything I created but would of obtained from this site or similar!

Sub Clearboxesofcheckness()

  Dim CBX As Object

    For Each CBX In ActiveSheet.CheckBoxes
      CBX.Value = xlOff
    Next CBX

End Sub

 

[ClaireD]

Thanks for your help. Unfortunately I made some mistakes in my original post. My code links check boxes to cells rather than ticks them. I need a macro that will unlink the check boxes from the cells.