CODEWITHSUNDEEP
excelvba
user2584367
user2584367

Reputation: 23

Out of stack space run time:1004 error

Private Sub Worksheet_Change(ByVal Target As Range)
     Worksheets("Order_Form").Cells(39, 2) = Environ("USERNAME")

    ' Pop up a warning when the user input more than 1000 cases
    If Target.Column = 7 And (Target.Row < 34 And Target.Row > 13) Then
        If Cells(Target.Row, Target.Column) > 1000 Then MsgBox "You are ordering more than 1000 cases", vbCritical
    End If

End Sub

This brings up an out of stack space error (runtime error 1004)

It usually happens when i try to edit any cell

Any one have ideas why?

Thanks!

Upvotes: 0

Views: 782

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

The first line is putting you into an endless loop, as you are initiating a worksheet change inside the Worksheet_Change event. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
     On Error GoTo err_handler
     Application.EnableEvents = False
     Worksheets("Order_Form").Cells(39, 2) = Environ("USERNAME")
     Application.EnableEvents = True

    ' Pop up a warning when the user input more than 1000 cases
    If Target.Column = 7 And (Target.Row < 34 And Target.Row > 13) Then
        If Cells(Target.Row, Target.Column) > 1000 Then MsgBox "You are ordering more than 1000 cases", vbCritical
    End If
err_handler:
Application.EnableEvents = True

End Sub

Upvotes: 2

trueamerican420
trueamerican420

Reputation: 221

Check out this article. It says its a common problem if one of the cells in your array has more than 911 characters (what a random number). And I see that yours can contain up to 1000. The link contains a work around that should hopefully help you out. Cheers!

http://support.microsoft.com/kb/818808

Upvotes: 0

Related Questions