WorkSheet Change Event Handler error.

**jetguy** · 01-13-2024, 09:55 PM

Hello,

I am fairly new to VBA. Can someone confirm if I am doing something wrong with my code to get errors or if there is a better method?
I am getting Out of Stack error and runtime error Worksheet Event Handler.
I have a couple of cells that I want to track changes to trigger a value change to "Please Select...". These cells are droplist.
For one of these dropdown lists, Range("B2") I want to see if it's a certain value if so then change the value of this other dropdown list Range("B14").

Thanks in advance.

Please Login or Register  to view this content.

**6StringJazzer** · 01-13-2024, 10:36 PM

When you have this event handler, it is called by the system every time there is a change in the sheet. Your Sub changes the sheet itself. So when it changes the sheet, it gets called. Then it changes the sheet again, and gets called again. Each time it gets called, a new copy of the Sub is loaded onto the call stack. This goes on until the stack space is exhausted and you get a runtime error.

At the beginning of your sub add

Please Login or Register  to view this content.

to prevent it from being called when it makes changes. Then at the end, add

Please Login or Register  to view this content.

**Marc L** · 01-14-2024, 09:56 AM

Originally Posted by jetguy

if I am doing something wrong with my code to get errors

Yes, bad Or syntax like bad logic as well !

So correcting the logic, the syntax and removing the useless :

PHP Code:


Private Sub Worksheet_Change(ByVal Target As Range) 
        Const C = "Please Select..." 
        Application.EnableEvents = False 
   With Target 
    If .Address = "$B$1" Then 
        [B2] = C 
    ElseIf .Address = "$B$2" Then 
        [B3,B14,B17] = C 
        If .Value = 305 Or .Value = 320 Or .Value = 340 Or .Value = 704 Then [B14] = "Material type1 or run new Macro event" 
    End If 
   End With 
        Application.EnableEvents = True 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**jetguy** · 01-14-2024, 02:21 PM

Thanks. I didn't realize it was doing that.

WorkSheet Change Event Handler error.

LinkBack

Thread Tools

Rate This Thread

Display

WorkSheet Change Event Handler error.

Re: WorkSheet Change Event Handler error.

Hello, try this !

Re: WorkSheet Change Event Handler error.

Thread Information

Users Browsing this Thread

Similar Threads

Runtime Error 28 - Out of Stack Space

[SOLVED] Runtime error 28 Call Stack

[SOLVED] Worksheet Change Event Causing Runtime Error 9

[SOLVED] Runtime error '28'..... out of stack space solution!!

How to change an event handler on error

runtime error, isempty, worksheet change event

[SOLVED] Run-time error '424' in VBA event handler on XL shutdown

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions