My excel macro silently exploded... :(

Hey, so I have a set of macro that were working, then dropped dead on the spot. before i paste the macros I'll give it some context

So I have several check boxes all link to cells range D41: D53. i have another check box that links to cell D40 which i was using as a "Select/ Unselect all" button (since changing the check box's linked cell changes the check box's status).

Also, i have the macros auto run when the file is opened.

so, the code:

Sub auto_open()

' Run the macro DidCellsChange any time a D40 is changed

ThisWorkbook.ActiveSheet.OnEntry = "DidCellsChange"
ThisWorkbook.ActiveSheet.OnCalculate = "DidCellsChange"
End Sub


Sub DidCellsChange()
Dim keyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
keyCells = "D40"

' Call the subroutine
KeyCellsChanged (keyCells)
End Sub

Sub KeyCellsChanged(ByVal keyCellsName As String)
Dim keyCells As Object
Dim Cell As Object

' Set keyCells to equal the cells that are defined in DidCellsChange()
Set keyCells = ActiveSheet.Range(keyCellsName)

' If the value in D40 is TRUE...
For Each Cell In Range("D41:D53")
If UCase(keyCells.Value) = "TRUE" Then

' Set all other check boxes to TRUE
Cell.Value = "TRUE"

Else
' Otherwise, set to FALSE
Cell.Value = "FALSE"
End If
Next Cell
End Sub

with this macro when the checkbox linking to D40 is changed, all check boxes linking to cells D41: D53 should mirror the D40 Checkbox


now i was using the .OnEntry to check if simply typing "TRUE" or "False" into D40 would work, and it does, but since checking a check box does not count as entering, i was using .OnCalculate that worked for a while.

Any help will be much appreciated.

Thanks!