Ensuring event does not fire when inserting/deleting rows

[Sintek]

Hi All

I have the below code which works when trying to enter a specific text in cell...Problem is it errors when I try to insert rows prior to typing in new info...Also crashes when trying to delete rows.
Is there a workaround to have event not fire when inserting or deleting rows...Other than "On error"

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:A5000")) Is Nothing Then
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
            Application.EnableEvents = True
        End If
    End If
End Sub

[MrShorty]

I don't think you can separately block "insert and delete" from the other actions that trigger the change event. I think you need to investigate what is different about Target when a row is inserted/deleted from when a single cell is entered. For testing, I added a Stop statement to the top of the procedure. When it entered debug mode, I checked the address of target when I inserted a row. Target was the entire new, empty row. Of course, the Left() function does not know how to extract the left two characters from an entire empty row, so it sets an error. Your main test here seems to be about editing/entering a single cell, so maybe a test for If Target.count>1 would be sufficient. Or test If Target.cells(1,1) is empty.

Is there more to this change procedure than you are showing here. In a completely different approach, why not use Excel's built in data validation to restrict entry to text beginning with DP? https://support.office.com/en-us/art...6-eff3ce5f7249 The help file includes an example "custom formula" data validation for "entry must begin with 'ID' and be more than 9 characters long", which should be easily adapted to "entry must begin with 'DP'".

[Arkadi]

IF we can assume that an entire row won't be copy/pasted then start the code with:

If Target.Columns.Count <> Columns.Count Then Exit Sub

Again, this will still prevent the macro from firing if a full row is copy/pasted.

For dealing with an Insert only, you could simply, directly after the If Not Intersect line put

If Target.Value = vbNullString then Exit Sub.

This would also prevent the messagebox if the user was clearing a part number.

[Sintek]

Hey there guys...

This solves...

If Target.Columns.Count = Columns.Count Then Exit Sub

That part lol, but now when my insert code runs...back to square 1

Sub Copy()
Sheet2.Range("A1:D1").Copy
Sheet1.Range("A9").PasteSpecial xlPasteValues
End Sub

[Sintek]

Hi Guys...

Thanks for responses...The only manual intervention will be insert row...Nothing else
Another macro will filter and delete rows later.
Edit...
Have tried all of above and none remedy the situation...Am I missing something...

[Winon]

Naand sintek,

Hier's hy, Vat hom!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    If Not Intersect(Target, Range("A7:A" & lr)) Is Nothing Then
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
       
            Application.EnableEvents = True
        End If
    End If
End Sub

Enjoy!

[Sintek]

Hey Tjaart...raas jou koppie vanaand...lol

No diffs...you just added a lrow...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    If Not Intersect(Target, Range("A7:A" & lr)) Is Nothing Then
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
       
            Application.EnableEvents = True
        End If
    End If
End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A7:A5000")) Is Nothing Then
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
            Application.EnableEvents = True
        End If
    End If
End Sub

Edit...
As per post 5...this solves the insert and delete rows and the specific text insert...

If Target.Columns.Count = Columns.Count Then Exit Sub

[Winon]

Hier is my bewys my maat,

Please try the attached Workbook any way you wish.

Regards.

[Sintek]

Winon...Your code does what mine in Post1 does...that was not my problem...
When inserting or deleting a row was my issue...your worksheet fails when doing so...
Runtime error 13 Type mismatch...

Hence this line solves that....As per post5.

If Target.Columns.Count = Columns.Count Then Exit Sub

What I need is for the code to be altered to allow for my insert macro to work as mentioned in post5...

Sub Copy()
Sheet2.Range("A1:D1").Copy
Sheet1.Range("A9").PasteSpecial xlPasteValues
End Sub

[Winon]

Hi sintek,

I am confused because it works on my side without any problems. Inserting or deleting lines, copy and paste values, you name it.

Sorry for wasting your time then.

Lekker slaap!

[Sintek]

nee f.k..Ek is ook confused...Tried code on all 3 pc,s and still errors...
Never wasted time...You got me started way back then on this stuff...

[MrShorty]

As I noted in my reply, the "type mismatch" error is because the Left() function requires a string input. Target is a range, and, when Target is a multi-cell range, Left() doesn't know what to do with the resulting array of values/empty.

In addition to the suggestions above, I could see trying something like Left(Target.Cells(1,1).Value,2), which will always pass a single value/empty to the Left() function. That should get rid of the type mismatch error, though I cannot be sure if that represents the desired programming logic when you are inserting rows or pasting multi-cell ranges.

[Winon]

@ MrShorty,

Did my sample Workbook also erred on your side?

Regards.

[MrShorty]

@ Winon: Yes, I got a "type mismatch" error when pasting multiple cells into a row starting in column A.

[Winon]

@ MrShorty,

Blast you MrShorty, nice catch. I never tested that.

The "Fix" maybe?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long

lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A7:A" & lr)) Is Nothing Then
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
       
            Application.EnableEvents = True
        End If
    End If
End Sub

Best Regards.

[Sintek]

Hi Guys...

Darn man Winon...Now your code works...strange though cause MrShorty suggested the code in Post 2...When trying it last night it did not work...This morning it does

Anyway, thank you both for sticking by me...I am a happy chappy

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
     If Not Intersect(Target, Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        'If Target.Columns.Count = Columns.Count Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
            Application.EnableEvents = True
        End If
    End If
End Sub

[Winon]

Phew, what a relief!

Thank you sintek, I was at my wits end as to why it did not work?

Glad it is "Solved" now, and thank you for the Rep+. I appreciate it!

Mag julle goeie reen ontvang.

[Winon]

Hey sintek,

And yet the thlot pickens.

The Code fires from Row 1 through Row 6 as well, which it should not do.

Solution, tweaking your Code;

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Not Intersect(Target, Range("A1:A6")) Is Nothing Then Exit Sub
     If Not Intersect(Target, Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        'If Target.Columns.Count = Columns.Count Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Left(Target.Value, 2) <> "DP" Then
            Application.EnableEvents = False
            MsgBox "PM NUMBER MUST START WITH DP", vbInformation, ""
            Target = ""
            Target.Activate
            Application.EnableEvents = True
        End If
    End If

End Sub

Geniet!

[Sintek]

Thanks Winon...Perfection