How to make a macro confirmation box pop up only once.

[wmmiller1973]

Hello all,

I am new to the forum as well as VBA, and I appreciate in advance any help I receive. I have a spreadsheet that gets data dumped in from another program. I need to delete every row in which column C has the text "TN VEN" or "TN QUAR". I have a data validation list that lets the user select which macro they want to run, and I have a worksheet change event that calls the macro when selected in my data validation list.

I have added a confirmation box, but it keeps repeating for each instance found. For instance, if there are four instances in which column C contains "TN QUAR", I have to click Yes five times before the rows are deleted. How can I make it so that I only have to press Yes once? Here is my code:

Please Login or Register  to view this content.

[stnkynts]

I don't think the issue is with your message box. I think the issue is with the worksheet change event being triggered everytime you delete a row. You can disable this by turning off enable events

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe a filter option, instead of the loop?

Please Login or Register  to view this content.

[wmmiller1973]

Thanks stnkynts for the quick suggestion...I tried this, but perhaps I placed it wrong? I was no longer prompted for confirmation, and no rows were deleted. My code looked like this:

Please Login or Register  to view this content.

I don't think the issue is with your message box. I think the issue is with the worksheet change event being triggered everytime you delete a row. You can disable this by turning off enable events

Please Login or Register  to view this content.

[wmmiller1973]

Hi John,

I tried this option...the first time I tried it, it worked well. I was prompted once to click yes, and all rows with "TN VEN" in column C. I ran it again, thinking nothing would happen because the data was already gone, but I got the prompt. I clicked "Yes" five times, before finally clicking "No" and it deleted my first five rows of data, including my column headers, which are in row 3.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[wmmiller1973]

Perfect! Thanks so much!

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.