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:
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]
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
Here is an example of a simple page calling that module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Call StatusBars(Target)
Application.ScreenUpdating = True
End Sub
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.
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.
The Question
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:
Dim TabStarted1 As Range
Set TabStarted1 = ActiveSheet.Range("A4:Z5").Find("Tab Started")
Dim TabStarted As Range
Set TabStarted = TabStarted1.Offset(0, 1)
Does anyone have any suggestions?
Bookmarks