Error with looping generating "Out of Stack" error.

[mstng50l]

I apologize in advance for my lack of VBA knowledge and vocabulary, so please try to keep any advice you have for me in simple terms.


I've created a macro that has two parts to it. The first (which works perfectly) activates a macro when a certain selection is made from a dropdown list. The second is creates a date tag to a cell after a user makes an input to certain range of cells (the range is a named DIM). When the user enters in data into a cell (either text or numeric) within the range, the macro works and puts a date stamp in the cell directly below the active cell. However, if the user deletes existing data within a cell in the "ApprovalRange" range, the macro begins to loop and occassionally generates a "Out of Stack" error. If the error message does not generate, I can tell something is not working properly because anything text that is in the formula bar looks as if it is waving, blinking, cycling...etc ( i don't know how else to describe it) I tried Ctrl+Brk and that does not stop the looping. The only way I can stop it is to close out of Excel completely.


Ultimately, what I'm trying to do in the second part is add some error proofing so the user doesn't crash the system if they need to delete/clear data from the range "ApprovalRange". I thought about data validation but I don't know if you can set you validation for text instead of numeric input.

Thank you for any advice you can give me on how to correct my looping problem



this section of code is on the tab "Data Entry"
the sub called "Tag_date" is located on the module tab

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As String
Dim ApprovalRange As String

ApprovalRange = Range("b20")

'if "Add New Licensee" is selected from the dropdown a macro (New_Licensee) runs to allow
' user to add in new name and abbreviation

If Target.Address = [e10].Address Then
Select Case Target
Case "Add New Licensee": New_Licensee
End Select

Else

'when an approval is entered in a date stamp is automatically generated and pasted (value)into the sheet.

If Not Intersect(Target, Range(ApprovalRange)) Is Nothing Then Tag_Date
End If
End Sub

 

[VoG]

Try

If Target.Address = [e10].Address Then
Application.EnableEvents = False
Select Case Target
Case "Add New Licensee": New_Licensee
End Select

Else

'when an approval is entered in a date stamp is automatically generated and pasted (value)into the sheet.

If Not Intersect(Target, Range(ApprovalRange)) Is Nothing Then Tag_Date
Application.EnableEvents = True
End If

 

[mstng50l]

Thank you for the quick response.

I tried adding in those two lines of code and I am still having the same problems. I checked my overall system status and when the error occurs, Excel begins to "hog" up the CPU way above its normal range. So at least I know what to check to see if this gets fixed.

Do you have any more suggestions?

 

[mstng50l]

I was able to find a solution to my problem. By adding in the "error proofing" section in bold, I gave the macro a way out of the loop. Thanks for all the help!!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As String
Dim ApprovalRange As String

ApprovalRange = Range("b20")

'if "Add New Licensee" is selected from the dropdown a macro (New_Licensee) runs to allow
' user to add in new name and abbreviation

If Target.Address = [e10].Address Then
Select Case Target
Case "Add New Licensee": New_Licensee
End Select

Else


'when an approval is entered in a date stamp is automatically generated and pasted (value)into the sheet.
If Not Intersect(Target, Range(ApprovalRange)) Is Nothing Then Tag_Date


'terminates the macro if something changes on the sheet not related to the other two options
If ActiveCell = vbNullString Then
Exit Sub