Using a worksheet parameter to fix an Out of Stack Space error

iPenguin

New Member
Joined
Sep 11, 2017
Messages
4
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]

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]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,215,025
Messages
6,122,732
Members
449,093
Latest member
Mnur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top