promalley2
New Member
- Joined
- Nov 16, 2009
- Messages
- 24
Greetings MVPs and Power Users!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o> </o>
I’m dealing with some issues in a piece of code that this message board helped me construct because the original scope has changed a bit. The code did kind of function properly at first, but once other areas of my project became automated, I realized I had to tweak the original solution. I’m having some trouble doing so I’m soliciting the pros once again. Thanks in advance for any time and advice or instruction that you can provide!<o></o>
<o> </o>
Background/What I’d Like to Accomplish:<o></o>
<o> </o>
1. I want to loop thorough an array of 6 sheets (and only those sheets), which are identical, and check a range of cells (on each sheet) for any non-zero values upon the (I think this is what I want) sheet calculate event—even if the sheet is not the active sheet. I actually want to have Excel continually check just the 6 sheet array for recalculation even when I’m working on a different sheet. (This a change from before where the active sheet was always where I was working when then calculation update took place.)<o></o>
2. If any sheet in the array has experienced an automated entry that has subsequently caused a value in the appropriate range, on any of the 6 sheets, to carry out a calculation (and multiple sheets can experience automated entries simultaneously),<o></o>
a. The sheet tab(s) should turn to yellow<o></o>
b. The name on the sheet tab(s) that are yellow will supply their name(s) to cells on a different sheet used for mail merging information.<o></o>
i. If the sheet tab is yellow, the name of the sheet will be used to evaluate two cases—the correct case will also send information to populate a cell on the mail merge sheet.<o></o>
3. Also, if any sheet(s) have had an automated value entered to it which causes the all the values in the appropriate range to return to zero, the corresponding sheet tab(s)—basically like a “toggle switch.”<o></o>
<o> </o>
Sample Range: (Appears on each of six sheets)<o></o>
<o></o>
0<o></o>
0<o></o>
0<o></o>
N<o></o>
N<o></o>
N<o></o>
N<o></o>
N<o></o>
N<o></o>
N<o></o>
0<o></o>
<o></o>
(The zero values are formulas that are checking for a change in 2 other cells, when a change takes place in either of those cells, the "zero" cells carryout calculation and become non-zero.)<o></o>
<o> </o>
The Present Issues:<o></o>
<o> </o>
The initial code that someone helped me develop was placed in “This Workbook” as it should have been, but it was checking every sheet of the workbook (13 of them) and thus caused some sheet tabs to turn yellow even though I didn’t want that. I’ve done a good bit of adding to the initial piece of code, which was a Boolean flagging system to check all the sheets for a yellow tab. You can see the way the Booleans are defined below; what I think is going on is: they are still configured to check every sheet. I tried to dimension the correct array of worksheets as an object with the hope of inserting a method into the Boolean definition, but needless to say that didn’t work. Please excuse my ignorance in object orientated programming—I’m still learning the first principles. Below is the code I’m working with right now:<o></o>
<o> </o>
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)<o></o>
'<o></o>
'create sheet2 as an object named "Mail_Merge"<o></o>
'Then create a boolean object named "Calc_Flag" that will decide if<o></o>
'the condition of "greater than zero" is truefor the range on any given sheet<o></o>
'<o></o>
Dim Mail_Merge As Sheet2<o></o>
Dim Calc_Flag As Boolean<o></o>
Dim Calc_Flag2 As Boolean<o></o>
'Dim Thing As Object<o></o>
'Set Thing = Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o></o>
'<o></o>
'Set the true conditions for Calc_Flag and Calc_Flag2 using a "CountIf" worksheet function<o></o>
'<o></o>
'Calc_Flag = Application.WorksheetFunction.CountIf(Range("b4:b17"), ">0")<o></o>
'Calc_Flag2 = Application.WorksheetFunction.CountIf(Range("b4:b17"), "<0")<o></o>
'<o></o>
'Initialize the sheet tab color to none (if Calc_Flag is false--none of the tabs are yellow)<o></o>
'<o></o>
Dim Cell As Range<o></o>
For Each Sh In Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o></o>
Sh.Tab.ColorIndex = xlNone<o></o>
Next Sh<o></o>
'<o></o>
'Initialize the range "P2:R2", on the Mail_Merge sheet to empty;<o></o>
'that way when the Calc_Flag is turned off, the cells lose the value inserted due to the Calc_Flag<o></o>
'<o></o>
Sheets("Mail_Merge").Range("P2").Value = ""<o></o>
Sheets("Mail_Merge").Range("Q2").Value = ""<o></o>
Sheets("Mail_Merge").Range("R2").Value = ""<o></o>
'<o></o>
'What happens below when the calculation is flagged:<o></o>
'1. The sheet tab becomes yellow if the calculation range is greater or less than zero<o></o>
'2. The sheet name with the calculation flag is printed in cell P2 on the mail merge sheet<o></o>
'3. The bury depth used in the calculation is printed in cell Q2 on the mail merge sheet<o></o>
'4. One of two messages is printed in cell R2 on the mail merge sheet based on<o></o>
' what the name of the calculation sheet is: if the sheet name is 56, 67, or 810<o></o>
' one response is given. If the calculation sheet name is 1012, 1214, or 1618<o></o>
' another response is given.<o></o>
'<o></o>
For Each Sh In Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o></o>
Calc_Flag = Application.WorksheetFunction.CountIf(Range("B4:B17"), ">0")<o></o>
Calc_Flag2 = Application.WorksheetFunction.CountIf(Range("B4:B17"), "<0")<o></o>
<o> </o>
For Each Cell In Sh.Range("B4:B17")<o></o>
<o></o>
If Calc_Flag Or Calc_Flag2 Then<o></o>
Sh.Tab.ColorIndex = 6<o></o>
Sheets("Mail_Merge").Range("P2").Value = Sh.Name<o></o>
Sheets("Mail_Merge").Range("Q2").Value = Sh.Range("E3").Value<o></o>
<o></o>
Select Case Sh.Name<o></o>
Case 56, 67, 810<o></o>
Sheets("Mail_Merge").Range("R2").Value = "2 spool check valves"<o></o>
Case 1012, 1214, 1618<o></o>
Sheets("Mail_Merge").Range("R2").Value = "1 in-line check valve"<o></o>
Case Else<o></o>
<o></o>
End Select<o></o>
<o></o>
End If<o></o>
<o></o>
Next Cell<o></o>
<o> </o>
Next Sh<o></o>
<o> </o>
'Sheets("Mail_Merge").Select<o></o>
<o> </o>
End Sub<o></o>
<o> </o>
From the code, you can see I’ve tried the Booleans in different locations. When I debug and step into the procedure line by line, when the calculation event fires, if the Booleans are at the top of the code one of them always initializes to “True” and causes the first sheet in the array to have the tab be yellow, even though every value in the range is zero or the letter “N”; then it gets caught in an infinite loop. If the Booleans appear in the For loop for each of the sheets, they initialize to “False” and never turn true even when one of the values in one or more of the cells in the ranges checked are non-zero. As the code stands right now, the Booleans are active in the For loop for the array of sheets. Anyone care to weigh in? Thanks again….<o></o>
<o> </o>
--Patrick<o></o>
fficeffice" /><o></o><o
> </o>I’m dealing with some issues in a piece of code that this message board helped me construct because the original scope has changed a bit. The code did kind of function properly at first, but once other areas of my project became automated, I realized I had to tweak the original solution. I’m having some trouble doing so I’m soliciting the pros once again. Thanks in advance for any time and advice or instruction that you can provide!<o
></o><o
> </o>Background/What I’d Like to Accomplish:<o
></o><o
> </o>1. I want to loop thorough an array of 6 sheets (and only those sheets), which are identical, and check a range of cells (on each sheet) for any non-zero values upon the (I think this is what I want) sheet calculate event—even if the sheet is not the active sheet. I actually want to have Excel continually check just the 6 sheet array for recalculation even when I’m working on a different sheet. (This a change from before where the active sheet was always where I was working when then calculation update took place.)<o
></o>2. If any sheet in the array has experienced an automated entry that has subsequently caused a value in the appropriate range, on any of the 6 sheets, to carry out a calculation (and multiple sheets can experience automated entries simultaneously),<o
></o>a. The sheet tab(s) should turn to yellow<o
></o>b. The name on the sheet tab(s) that are yellow will supply their name(s) to cells on a different sheet used for mail merging information.<o
></o>i. If the sheet tab is yellow, the name of the sheet will be used to evaluate two cases—the correct case will also send information to populate a cell on the mail merge sheet.<o
></o>3. Also, if any sheet(s) have had an automated value entered to it which causes the all the values in the appropriate range to return to zero, the corresponding sheet tab(s)—basically like a “toggle switch.”<o
></o><o
> </o>Sample Range: (Appears on each of six sheets)<o
></o><o
></o>0<o
></o>0<o
></o>0<o
></o>N<o
></o>N<o
></o>N<o
></o>N<o
></o>N<o
></o>N<o
></o>N<o
></o>0<o
></o><o
></o>(The zero values are formulas that are checking for a change in 2 other cells, when a change takes place in either of those cells, the "zero" cells carryout calculation and become non-zero.)<o
></o><o
> </o>The Present Issues:<o
></o><o
> </o>The initial code that someone helped me develop was placed in “This Workbook” as it should have been, but it was checking every sheet of the workbook (13 of them) and thus caused some sheet tabs to turn yellow even though I didn’t want that. I’ve done a good bit of adding to the initial piece of code, which was a Boolean flagging system to check all the sheets for a yellow tab. You can see the way the Booleans are defined below; what I think is going on is: they are still configured to check every sheet. I tried to dimension the correct array of worksheets as an object with the hope of inserting a method into the Boolean definition, but needless to say that didn’t work. Please excuse my ignorance in object orientated programming—I’m still learning the first principles. Below is the code I’m working with right now:<o
></o><o
> </o>Private Sub Workbook_SheetCalculate(ByVal Sh As Object)<o
></o>'<o
></o>'create sheet2 as an object named "Mail_Merge"<o
></o>'Then create a boolean object named "Calc_Flag" that will decide if<o
></o>'the condition of "greater than zero" is truefor the range on any given sheet<o
></o>'<o
></o>Dim Mail_Merge As Sheet2<o
></o>Dim Calc_Flag As Boolean<o
></o>Dim Calc_Flag2 As Boolean<o
></o>'Dim Thing As Object<o
></o>'Set Thing = Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o
></o>'<o
></o>'Set the true conditions for Calc_Flag and Calc_Flag2 using a "CountIf" worksheet function<o
></o>'<o
></o>'Calc_Flag = Application.WorksheetFunction.CountIf(Range("b4:b17"), ">0")<o
></o>'Calc_Flag2 = Application.WorksheetFunction.CountIf(Range("b4:b17"), "<0")<o
></o>'<o
></o>'Initialize the sheet tab color to none (if Calc_Flag is false--none of the tabs are yellow)<o
></o>'<o
></o>Dim Cell As Range<o
></o>For Each Sh In Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o
></o>Sh.Tab.ColorIndex = xlNone<o
></o>Next Sh<o
></o>'<o
></o>'Initialize the range "P2:R2", on the Mail_Merge sheet to empty;<o
></o>'that way when the Calc_Flag is turned off, the cells lose the value inserted due to the Calc_Flag<o
></o>'<o
></o>Sheets("Mail_Merge").Range("P2").Value = ""<o
></o>Sheets("Mail_Merge").Range("Q2").Value = ""<o
></o>Sheets("Mail_Merge").Range("R2").Value = ""<o
></o>'<o
></o>'What happens below when the calculation is flagged:<o
></o>'1. The sheet tab becomes yellow if the calculation range is greater or less than zero<o
></o>'2. The sheet name with the calculation flag is printed in cell P2 on the mail merge sheet<o
></o>'3. The bury depth used in the calculation is printed in cell Q2 on the mail merge sheet<o
></o>'4. One of two messages is printed in cell R2 on the mail merge sheet based on<o
></o>' what the name of the calculation sheet is: if the sheet name is 56, 67, or 810<o
></o>' one response is given. If the calculation sheet name is 1012, 1214, or 1618<o
></o>' another response is given.<o
></o>'<o
></o>For Each Sh In Worksheets(Array("56", "67", "810", "1012", "1214", "1618"))<o
></o>Calc_Flag = Application.WorksheetFunction.CountIf(Range("B4:B17"), ">0")<o
></o>Calc_Flag2 = Application.WorksheetFunction.CountIf(Range("B4:B17"), "<0")<o
></o><o
> </o>For Each Cell In Sh.Range("B4:B17")<o
></o><o
></o>If Calc_Flag Or Calc_Flag2 Then<o
></o>Sh.Tab.ColorIndex = 6<o
></o>Sheets("Mail_Merge").Range("P2").Value = Sh.Name<o
></o>Sheets("Mail_Merge").Range("Q2").Value = Sh.Range("E3").Value<o
></o><o
></o>Select Case Sh.Name<o
></o>Case 56, 67, 810<o
></o>Sheets("Mail_Merge").Range("R2").Value = "2 spool check valves"<o
></o>Case 1012, 1214, 1618<o
></o>Sheets("Mail_Merge").Range("R2").Value = "1 in-line check valve"<o
></o>Case Else<o
></o><o
></o>End Select<o
></o><o
></o>End If<o
></o><o
></o>Next Cell<o
></o><o
> </o>Next Sh<o
></o><o
> </o>'Sheets("Mail_Merge").Select<o
></o><o
> </o>End Sub<o
></o><o
> </o>From the code, you can see I’ve tried the Booleans in different locations. When I debug and step into the procedure line by line, when the calculation event fires, if the Booleans are at the top of the code one of them always initializes to “True” and causes the first sheet in the array to have the tab be yellow, even though every value in the range is zero or the letter “N”; then it gets caught in an infinite loop. If the Booleans appear in the For loop for each of the sheets, they initialize to “False” and never turn true even when one of the values in one or more of the cells in the ranges checked are non-zero. As the code stands right now, the Booleans are active in the For loop for the array of sheets. Anyone care to weigh in? Thanks again….<o
></o><o
> </o>--Patrick<o
></o>
