In my workbook I have a set of status boxes with code that allows users to check them based on the current status of that tab. The code is the same, so recently I pulled it into its own module. This code is called on every page of the workbook. Recently, I've been getting "Out of Stack Space" errors relating to that code call, and sometimes a "With block variable not set" error that highlights one of the variable setting lines in the status bars module itself. Here's a condensed version of the module code, along with an example of the module call:
Status bars module:
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub StatusBars(ByVal Target As Range)
Dim TabStarted1 As Range
Set TabStarted1 = ActiveSheet.Range("A4:Z5").Find("Tab Started")
Dim TabStarted As Range
Set TabStarted = TabStarted1.Offset(0, 1)
[two more examples of the same]
Status bars module:
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub StatusBars(ByVal Target As Range)
Dim TabStarted1 As Range
Set TabStarted1 = ActiveSheet.Range("A4:Z5").Find("Tab Started")
Dim TabStarted As Range
Set TabStarted = TabStarted1.Offset(0, 1)
[two more examples of the same]
Code:
If Not Intersect(Target, TabStarted) Is Nothing Then
If Target.Cells.Count = 2 Then
If WorksheetFunction.CountA(Target) = 0 Then
TabStarted.Value = "X"
TabStarted.HorizontalAlignment = xlCenter
TabStarted.Font.Size = 25
TabStarted.Interior.Color = RGB(255, 255, 0)
Design.Interior.Color = RGB(255, 255, 255)
Design.Value = ""
Configurations.Interior.Color = RGB(255, 255, 255)
Configurations.Value = ""
ActiveSheet.Tab.Color = RGB(255, 255, 0)
Else
TabStarted.Interior.Color = RGB(255, 255, 255)
TabStarted.Value = ""
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
End If
End If
End If
[etc]
End Sub</code>
[COLOR=#333333]Here is an example of a simple page calling that module:[/COLOR]
[COLOR=#333333][IMG]http://icons.iconarchive.com/icons/double-j-design/ravenna-3d/24/File-Copy-icon.png[/IMG]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Call StatusBars(Target)
Application.ScreenUpdating = True
End Sub</code>[/COLOR]
[COLOR=#333333]It was suggested to me elsewhere that I could use Application.EnableEvents = False to resolve this. And while I haven't seen Out of Stack Space as often, I still get the "With block variable not set" error. Worse still, if I get an error midway through the code on any sheet, EnableEvents hasn't yet been turned back on, so now I can't do anything in Excel. [/COLOR]
[COLOR=#333333]The other suggestion was that because ActiveSheet is a global variable, it stays on the stack, therefore I should try to make ActiveSheet a parameter of my function.[/COLOR]
[B]The Question[/B][COLOR=#333333]The problem is, I'm not entirely sure how to do this. While I know I can declare my Sub as: Sub StatusBars(ByVal Target As Range, ActiveSheet As Worksheet) and call it by "Call StatusBars(Target, ActiveSheet), I don't know what I need to do to these lines to make them work correctly and not stay on the stack:
[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Dim TabStarted1 As Range
Set TabStarted1 = [B]ActiveSheet.Range("A4:Z5").Find("Tab Started") [/B]
Dim TabStarted As Range
Set TabStarted = TabStarted1.Offset(0, 1)</code>[COLOR=#333333]
Does anyone have suggestions for what I should do once I have passed the ActiveSheet as a paramter to the function?
[/COLOR]