Hello,
This is a long post, if you are too lazy, here is the issue.
Need to make automatic changes to cells that will activate a "Run when Values Change Macro" without going into a loop forever.
Getting "The object invoked has disconnected from...." when I've tried to link it
I've got a job that wants sections of a sheet to hide/Unhide depending on the presence of several key cells and several check boxes, Basic logic goes like this.
Is Checkbox 1 ticked?
Are all Section 1 required cells filled? Hide Section 1 and start looking at Section 2
Yes No
Show next section Hide Next Section
Move onto next section checks.
Rinse, repeat for all the sections.
Issue is that Checkbox 2 can be ticked when Checkbox 1 isn't, so we need a way so that it only checks the Section 1 cells when Checkbox 1 is clicked, Also we need that the hide effect carries on down, so that if the checkbox 1 is clicked, but not all cells are there, all sections afterwards are hidden.
Not sure I've been that clear so in short. show the next section only if ALL previous checked sections have their cells filled.
So far I've got it by having a few cells have values Hide/Show based upon the logic and then the hiding macro just references that. It's going great, but I can't seem to get it running automatically. Current set up is the Logic Macro is based around when the Checkboxes are clicked, that then triggers one macro for applying the hide/show, I also have a copy of the macro for Hiding/Showing that runs automatically when anything is changed.
It works okay but is VERY clunky because although when a checkbox is clicked it works fine everything when updating cells it won't update the logic cells so the update macro is running wrong.
I tried linking the logic and auto update macro but that screwed up as it came up with "The object invoked has disconnected from...." I'd assume because it was changing a cell, activating the auto update, thus changing the cells etc.
To counter this I tried making it so it would only run the auto macro when a cell that wasn't the Hide/Show but didn't help... Any ideas?
These are some of the codes used, it's no where near complete
This is a long post, if you are too lazy, here is the issue.
Need to make automatic changes to cells that will activate a "Run when Values Change Macro" without going into a loop forever.
Getting "The object invoked has disconnected from...." when I've tried to link it
I've got a job that wants sections of a sheet to hide/Unhide depending on the presence of several key cells and several check boxes, Basic logic goes like this.
Is Checkbox 1 ticked?
Yes NoAre all Section 1 required cells filled? Hide Section 1 and start looking at Section 2
Yes No
Show next section Hide Next Section
Move onto next section checks.
Rinse, repeat for all the sections.
Issue is that Checkbox 2 can be ticked when Checkbox 1 isn't, so we need a way so that it only checks the Section 1 cells when Checkbox 1 is clicked, Also we need that the hide effect carries on down, so that if the checkbox 1 is clicked, but not all cells are there, all sections afterwards are hidden.
Not sure I've been that clear so in short. show the next section only if ALL previous checked sections have their cells filled.
So far I've got it by having a few cells have values Hide/Show based upon the logic and then the hiding macro just references that. It's going great, but I can't seem to get it running automatically. Current set up is the Logic Macro is based around when the Checkboxes are clicked, that then triggers one macro for applying the hide/show, I also have a copy of the macro for Hiding/Showing that runs automatically when anything is changed.
It works okay but is VERY clunky because although when a checkbox is clicked it works fine everything when updating cells it won't update the logic cells so the update macro is running wrong.
I tried linking the logic and auto update macro but that screwed up as it came up with "The object invoked has disconnected from...." I'd assume because it was changing a cell, activating the auto update, thus changing the cells etc.
To counter this I tried making it so it would only run the auto macro when a cell that wasn't the Hide/Show but didn't help... Any ideas?
These are some of the codes used, it's no where near complete
Code:
Sub TransferChecks()
If chkTransfer.Value = True Then
If (ActiveSheet.Range("D12:I12").Text = "") Or _
(ActiveSheet.Range("O12:T12").Text = "") Or _
(ActiveSheet.Range("O15:T15").Text = "") Or _
(ActiveSheet.Range("F18:K18").Text = "") Or _
(ActiveSheet.Range("F19:K19").Text = "") Then
Range("AG1").Formula = "Hide"
Else
Range("AG1").Formula = "Show"
End If
Else
Range("AG1").Formula = "Show"
End If
End Sub
Sub ProcessChecks()
If chkProcessData.Value = True Then
If (ActiveSheet.Range("K68:L68").Text = "") Or (ActiveSheet.Range("M68:N68").Text = "") Or _
(ActiveSheet.Range("K71:L71").Text = "") Or (ActiveSheet.Range("M71:N71").Text = "") Or _
(ActiveSheet.Range("K72:L72").Text = "") Or (ActiveSheet.Range("M72:N72").Text = "") Or _
(ActiveSheet.Range("K73:L73").Text = "") Or (ActiveSheet.Range("M73:N73").Text = "") Or _
(ActiveSheet.Range("K74:L74").Text = "") Or (ActiveSheet.Range("M74:N74").Text = "") Or _
(ActiveSheet.Range("K76:L76").Text = "") Or (ActiveSheet.Range("M76:N76").Text = "") Or _
(ActiveSheet.Range("K80:L80").Text = "") Or (ActiveSheet.Range("M80:N80").Text = "") Or _
(ActiveSheet.Range("K81:L81").Text = "") Or (ActiveSheet.Range("M81:N81").Text = "") Or _
(ActiveSheet.Range("K82:L82").Text = "") Or (ActiveSheet.Range("M82:N82").Text = "") Or _
(ActiveSheet.Range("K83:L83").Text = "") Or (ActiveSheet.Range("M83:N83").Text = "") Or _
(ActiveSheet.Range("K84:L84").Text = "") Or (ActiveSheet.Range("M84:N84").Text = "") Or _
(ActiveSheet.Range("K85:L85").Text = "") Or (ActiveSheet.Range("M85:N85").Text = "") Or _
(ActiveSheet.Range("K86:L86").Text = "") Or (ActiveSheet.Range("M86:N86").Text = "") Then
Range("AG2").Formula = "Hide"
Else
Range("AG2").Formula = "Show"
End If
Else
Range("AG2").Formula = "Show"
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
'transfer
If chkTransfer.Value = False Then
ActiveSheet.Range("TransferSheet").EntireRow.Hidden = True
Else
ActiveSheet.Range("TransferSheet").EntireRow.Hidden = False
End If
'rev history
If chkRevision.Value = False Then
ActiveSheet.Range("RevisionHistory").EntireRow.Hidden = True
Else
If ActiveSheet.Range("AG1").Text = "Hide" And chkTransfer.Value = True Then
ActiveSheet.Range("RevisionHistory").EntireRow.Hidden = True
Else
ActiveSheet.Range("RevisionHistory").EntireRow.Hidden = False
End If
End If
'process data
If chkProcessData.Value = False Then
ActiveSheet.Range("ProcessData").EntireRow.Hidden = True
ActiveSheet.Range("ProcessData2").EntireRow.Hidden = True
Else
If ActiveSheet.Range("AG1").Text = "Hide" Then
ActiveSheet.Range("ProcessData").EntireRow.Hidden = True
ActiveSheet.Range("ProcessData2").EntireRow.Hidden = True
Else
ActiveSheet.Range("ProcessData").EntireRow.Hidden = False
ActiveSheet.Range("ProcessData2").EntireRow.Hidden = False
End If
End If
'bundle
If chkBundle.Value = False Then
ActiveSheet.Range("BundleSpec1").EntireRow.Hidden = True
ActiveSheet.Range("BundleSpec2").EntireRow.Hidden = True
Else
If ActiveSheet.Range("AG2").Text = "Show" And _
ActiveSheet.Range("AG1").Text = "Show" Then
ActiveSheet.Range("BundleSpec1").EntireRow.Hidden = False
ActiveSheet.Range("BundleSpec2").EntireRow.Hidden = False
Else
ActiveSheet.Range("BundleSpec1").EntireRow.Hidden = True
ActiveSheet.Range("BundleSpec2").EntireRow.Hidden = True
End If
End If
'vessel
If chkVessel.Value = False Then
ActiveSheet.Range("VesselSpec1").EntireRow.Hidden = True
ActiveSheet.Range("VesselSpec2").EntireRow.Hidden = True
Else
ActiveSheet.Range("VesselSpec1").EntireRow.Hidden = False
ActiveSheet.Range("VesselSpec2").EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub
Private Sub chkBundle_Click()
Call TransferChecks
Call ProcessChecks
Call CheckRangesAndPageBreaks
End Sub
