Solved change by val out of stack error

August 8, 2018 at 10:34:12
Specs: Windows 7
I am using this code to enable some shapes based on values in cells. It works perfectly until I added the last part (marked with asterisks), I need this part to update a cell based on the value of D26.. I am getting an "Out of stack space" error.

any suggestions on how I can fix this?

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Range("D25").Value > Range("G11").Value Then
   ActiveSheet.Shapes("HousingX").Visible = True
   ActiveSheet.Shapes("HousingCheck").Visible = False
End If
If Range("D25").Value <= Range("G11").Value Then
   ActiveSheet.Shapes("HousingX").Visible = False
   ActiveSheet.Shapes("HousingCheck").Visible = True
End If

If Range("D26").Value > Range("G12").Value Then
   ActiveSheet.Shapes("DTIX").Visible = True
   ActiveSheet.Shapes("DTICheck").Visible = False
End If
If Range("D26").Value <= Range("G12").Value Then
   ActiveSheet.Shapes("DTIX").Visible = False
   ActiveSheet.Shapes("DTICheck").Visible = True
End If
'*****part that is not working *****
If Range("D26").Value > 0.45 Then
   Range("D15").Value = Sheets("Closing Costs").Range("BM102").Value
Else
   Range("D15").Value = Sheets("Closing Costs").Range("BM97").Value
End If
'*****

End Sub

message edited by mecerrato


See More: change by val out of stack error

Reply ↓  Report •

#1
August 8, 2018 at 13:11:58
✔ Best Answer
This is just a guess 'cuz I don't have your sheet to test my theory against...but you can.

A Worksheet_Change macro runs with any change to a cell or cells in a sheet. That includes any changes that the macro itself makes to a cell. IOW, when a Worksheet_Change macro makes a change to a sheet, it basically "calls itself" and runs again from the beginning.

You can watch this happen by using the Single Step mode (F8) in the VBE and stepping through the instructions. As soon as it executes the change made by your new If section, I'm guessing that it will jump back up to the top and start over.

This recursive running has been known to cause some pretty strange errors.

One way to avoid the issue is to include these instructions:

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   
     ' Do stuff here
     
   Application.EnableEvents = True
End Sub

When Events are disabled, the change made by the code itself will not cause the Worksheet_Change code to start again, but you must enable Events before the codes ends or user changes won't trigger the code either.

In addition, when you use that method you should include an error handling routine in case an error occurs in the "Do stuff here" section. If the code throws up an error before executing the Application.EnableEvents = True instruction, then Events will remain disabled. That mean that no event macros (Worksheet_Change, Worksheet_SelectionChange, Workbook_Open, etc.) will run until event are re-enabled. The error handler must enable Events before the code exits.

Lots of hits on Google on error handling for event code.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
August 8, 2018 at 16:21:31
Thank you for the thorough explanation, I will begin some googling :-)

Reply ↓  Report •
Related Solutions


Ask Question