Don't run Sub Worksheet_Change(ByVal Target As Range) after all cells in the worksheet has been cleared

Question

I have a macro where i import a text file and update some elements of this file using the macro and then re-create the text file with the updated elements. I am validating some of the cells in a particular worksheet (USERSHEET)to make sure the user entries are correct and using the below Sub:

    Option Explicit
Public Rec_Cnt As Integer
Private Sub Worksheet_Change(ByVal Target As Range)

Rec_Cnt = Sheets("MD").Cells(3, 7)
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

Set Rng1 = Range("E2:E" & Rec_Cnt)
Set Rng2 = Range("K2:K" & Rec_Cnt)
Set Rng3 = Range("Q2:Q" & Rec_Cnt)


If Not Application.Intersect(Target, Rng1) Is Nothing Then
If Len(Target) > 10 Then
   Call Original_Ticket_Error
Exit Sub
End If
ElseIf Not Application.Intersect(Target, Rng2) Is Nothing Then
If Len(Target) > 10 Then
   Call Original_Cnj_Ticket_Error
Exit Sub
End If
ElseIf Not Application.Intersect(Target, Rng3) Is Nothing Then
If Len(Target) > 10 Then
   Call Original_Ticket_Error
Exit Sub
End If
End If

End Sub

Sub Original_Ticket_Error()
MsgBox "Original Ticket Number is more 10 characters"
End Sub

Sub Original_Cnj_Ticket_Error()
MsgBox "Original Conj. Ticket Number is more 10 characters"
End Sub

=============================================================================== Once the text file is created with the updated columns I am clearing all the cells in the USERSHEET. However, I get a run-time error '13' for type mismatch

I wanted to check how can I avoid calling the Private Sub Worksheet_Change(ByVal Target As Range) after the worksheet(USERSHEET) is cleared

Any help is much appreciated.

Thanks, sachin

Edit:

Code used to clear usersheet:

Sub Clear_User_Sheet()
    Sheets("UserSheet").Select
    Range("A2:R100002").Select
    Application.Wait (Now + TimeValue("0:00:01"))
    Selection.Delete
    Application.EnableEvents = False
    Application.Wait (Now + TimeValue("0:00:01"))
    Selection.Delete
    Selection.Delete
    Sheets("Control Panel").Select
End Sub

Your code assumes a single cell is changed so if that's not the case, you have to loop through the target and check each cell (or exit the sub if count>1). — SJR
You could also just disable events before clearing the sheet and enable them again afterwards. — CLR
@CLR - Many thanks for your response. I did add the enable Application.EnableEvents = False but still gives me the error. Below is the code i use to clear the usersheet:Sub Clear_User_Sheet() Sheets("UserSheet").Select Range("A2:R100002").Select Application.Wait (Now + TimeValue("0:00:01")) Selection.Delete Application.EnableEvents = False Application.Wait (Now + TimeValue("0:00:01")) Selection.Delete Selection.Delete Sheets("Control Panel").Select End Sub — Sachin S
Where do you call the Clear_User_Sheet? I can't see it in the code in the question. Selection.Delete will cause the WorkSheet.Change event to fire unless you disable events beforehand, and remember to enable them afterwards. — Darren Bartrup-Cook
@DarrenBartrup-Cook - I am calling the Sub Clear_User_Sheet() after the file is created post the updates. And the Selection.Delete is within this sub and I am aware that this Selection.Delete is calling the Worksheet.change event. How can i avoid it calling this ?? I am not sure if I am doing the right thing here ? — Sachin S

CLR CLR · Accepted Answer · 2018-05-08T13:07:32

Try this version of Clear_User_Sheet instead:

Sub Clear_User_Sheet()
    Application.EnableEvents = False
    Sheets("UserSheet").Range("A2:R100002").Delete
    Application.EnableEvents = True
End Sub

PS. If you've used the code that you suggested in your edited answer, you may well find that EnableEvents is currently set to False - you'll want to correct that before running anything else.

Don't run Sub Worksheet_Change(ByVal Target As Range) after all cells in the worksheet has been cleared

1 Answers