Runtime error '28': Out of stack space in Excel VBA

Question

I try to a create a workbook for my requirement. The first sheet include a cell which type is 'Text' and it is for DATE value.

I add Workbook_Open method for set today date when open the workbook as shown below.

Private Sub Workbook_Open()
     Sheet1.Range("F6") = Date
End Sub

And I also add Worksheet_Change method for sheet of that cell. That is for validation check as below.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$F$6" Then

        'Getting insertion date.
        insertionDate = Sheet1.Range("F6")

        'If date field is not empty
        If insertionDate <> "" Then

            Call MsgBox("Insertion Date must be inserted.")

        End If

    End If

End Sub

After that, I tested my code. When open the work book, I got the following error.

Run-time error '28': 
Out of stack space

When click 'Debug' button, the cursor shown at the first line of Worksheet_Change method.

I has tried everything what I thought. But nothing is going on. Help me. Thank You.

R.Katnaan · Accepted Answer · 2015-06-17 08:42:37Z

1

I got it with this code. It is not satisfied for me but my problem is solved.

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Target.Address = "$F$6" Then

        'Getting insertion date.
        insertionDate = Sheet1.Range("F6")

        'If date field is not empty
        If insertionDate <> "" Then

            Call MsgBox("Insertion Date must be inserted.")

        End If

    End If

    Application.EnableEvents = True

End Sub

answered Jun 17, 2015 at 8:42

R.Katnaan

2,5364 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Monkawee Maneewalaya Over a year ago

May be your code struck in recursive trigger event.

R.Katnaan Over a year ago

No, I think that is cause of "Multiple Sub". Thanks for your advice.

Monkawee Maneewalaya · Accepted Answer · 2015-06-16 10:20:30Z

0

Msgbox not need Call statement. Try to remove Call and test again. And I have some reference from https://support.microsoft.com/en-us/kb/126090?wa=wsignin1.0. Its may be explain your error.

answered Jun 16, 2015 at 10:20

Monkawee Maneewalaya

1501 gold badge1 silver badge14 bronze badges

Comments

sancho.s ReinstateMonicaCellio · Accepted Answer · 2015-06-16 11:33:28Z

0

Possible sources of error marked in the code

Option Explicit   ' Candidate

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$6" Then

        'Getting insertion date.
        Dim insertionDate as String   ' Candidate
        insertionDate = Sheet1.Range("F6").Text   ' Candidate

        'If date field is not empty
        If insertionDate <> "" Then
            MsgBox("Insertion Date must be inserted.")   ' Candidate
        End If

    End If
End Sub

Make sure you placed Worksheet_Change in the sheet module.

answered Jun 16, 2015 at 11:33

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges212 bronze badges

1 Comment

R.Katnaan Over a year ago

Actually, 'insertionDate' is declared as Public in other module. Thanks for your advice.

Collectives™ on Stack Overflow

Runtime error '28': Out of stack space in Excel VBA

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related