Code to keep a macro from activating more than once?

[debbiesh]

Hello all.

I have a macro I want the user to click once and no more. Is there something I can add to the code that will do such a thing?

Edited to add: The macro saves the file to a specific location. The problem is when it's run more than once, it causes an error message which freaks the user out. lol

Thanks.

[tigeravatar]

debbiesh,

When I want to do something like that, I reserve an out of the way cell (usually the cell in the first row the last column of the file) to store either 0 or 1. If it is a 0, then let the macro run, and at the end of the macro, change the cell to a 1. So that when the macro goes to run again, it checks the cell which is now a 1 and so the macro exits. It looks like this:

Please Login or Register  to view this content.

You can change that cell back to a 0 using a different macro, or the workbook_open event, or change it manually if you want the macro to run again.

[debbiesh]

Thank you for your reply. I'll give it a shot asap and let you know what happens.

[debbiesh]

Keep getting 404 error. What am I doing wrong?

Please Login or Register  to view this content.

[tigeravatar]

debbiesh,

This doesn't exist: Cells(AZ2, Columns.Count)
If you want to use AZ2, then instead use: Range("AZ2")

[debbiesh]

Ahh, right! Thanks. So it saves correctly when I click the button to activate the macro, and AZ2 changes to 1. Perfect.
Then when I click the button a second time, I get a 404 error message. What would cause the error at this point?

[tigeravatar]

You need to put all of the code in the IF statement. You currently have the activeworkbook.save and the msgbox outside of the if statement

[debbiesh]

Hey, I'm learning here. LOL It works perfectly now! Thanks so much, tigeravatar.

[tigeravatar]

You're very welcome