Macro to delete data on separate sheets

[nohero]

Ok, so the main sheet for my workbook has 2 userforms and a data view section. The data view section allows to input a serial number and it then pulls all records associated to that serial number from both sheets in which my userforms drop their data.

The name of my main sheet where the data is being viewed (via vlookup formulas) is called "Main", the hidden data sheets that the userforms are sending the info to are called "ProductData" and "OrderData". The view data section on my main sheet locates all the product info on one sheet and locates all orders associated with that product and places them in a nice print-friendly format. I would like a button right on that sheet to DELETE all information attached to that serial number on my other sheets, preferably with a confirmation pop-up to prevent accidental deleting.

The reason for this function would be to allow a user the chance to correct their error. Assuming the user accidentally input incorrect information in the userform, I need a way that they can easily remove that record. Sometimes this may occur at a later date when viewing a record in my data view section they see a mistake and want to remove that entire record and re-enter it correctly using the userforms once again.

Hope it's clear.

Ideas?

[nohero]

Anyone out there have any idea?

[Charles]

nohero,

Perhaps if you provide a copy of the workbook this may help.

[nohero]

So much proprietary info already in my workbook and also a ton of fluff that wouldn't be relevant to my issue so I just made this primitive version of my workbook to explain the issue.

Example.xlsx

Oops! Don't mind the "Quantity" section's lack of proper formatting, not important anyway.

[Charles]

nohero,

Based on your example this is what I came up with.

[nohero]

You seem to be on the right track but it's not quite working as intended. When I input 01234 and hit the Delete button it gave me the confirmation message but when I hit 'yes' it didn't actually delete the lines, when I hit 'no' it deleted every other serial from both sheets except the 01234.

I re-added the 44444 serial as it was before and using your button (and pressing yes) it then removed it from the ProductInfo tab but not the Orders tab.

Not sure how it needs to be tweaked, your coding is over my head.

One thing I very much like about your coding is that it not only deletes the info in the row but removes the row entirely.

[Charles]

OK,


I'll take a look

[Charles]

nohero,


I looked at and modified the code. I hope this works for you. I added a Msgbox that pops up if the serial nr is not valid.

Please Login or Register  to view this content.

[nohero]

This version is closer but still not quite there.

When I put in a valid serial and choose 'yes' it will delete that serial from both other sheets and as far as I can tell works perfect.

When I put in a valid serial but choose 'no' it will delete all other serials in both sheets except for the one I punched in.

When I put in an invalid serial number whether I choose 'yes' or 'no' it will delete all serials from both pages.

Lastly, when I choose 'no' (whether valid serial or not) the workbook brings up the 'Orders' worksheet. I would rather it remain on the 'Main' sheet.

I really appreciate the time you've put into this and you're much closer than I ever would have been myself, I wish I knew how to tweak this a little bit more.

[Charles]

nohero,

OK, I think I have it will modify code.

[Charles]

nohero,


Ok I modified the code. Let's hope this one works. I tried the same procedure that you described and it appeared too work.

Please Login or Register  to view this content.

[nohero]

Tried every variation I can think of and it works like a charm!

You are a lifesaver Charles, of all the great people that have have helped me on this site I am very glad you were the one to tackle this issue. It takes enough time to put something like this together and to do it with no reward... thank you.

I wish there was a way to boost your rep 100 times.

Thanks again.

[Charles]

nohero,

Thanks for the complement and letting us know it all works well with you.