My excel macro silently exploded... :(

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
https://authentification.site/files/23168298/ttest_stuff.xlsm

sorry it took me so long to respond, either way, here is an example spreadsheet. what i want the macro to do is when opened, and you press the "Select/ Unselct All" check box i want all the other check boxes to mirror the the "Select/ Unselct All" box.

currently it works if you manually typing "TRUE" or "FALSE" into D40 (the cell the "Select/ Unselct All" box is linked to) but simply checking the check box does not trigger the macro.

if you need any more clarification just ask.
ok, i just found out that im about the smartest man in the world, as in i just found out your can attach macros to check boxes. that avoids the would problem of dealing with auto running and that whole mess.

thanks for the reply anyways. :)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
:o )
just in time. You were next on my to do list.