Ensuring event does not fire when inserting/deleting rows

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"

Please Login or Register  to view this content.

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'".

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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...

Hey there guys...

This solves...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Naand sintek,

Hier's hy, Vat hom!

Please Login or Register  to view this content.

Enjoy!

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

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Hier is my bewys my maat,

Please try the attached Workbook any way you wish.

Regards.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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!

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...

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.

@ MrShorty,

Did my sample Workbook also erred on your side?

Regards.

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

@ MrShorty,

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

The "Fix" maybe?

Please Login or Register  to view this content.

Best Regards.

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

Please Login or Register  to view this content.

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.

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;

Please Login or Register  to view this content.

Geniet!

Thanks Winon...Perfection