Button loses connection to macro when I "Save As".

[Peskarik]

Hi, everyone

Another Excel VBA question.

SETUP:
I have an Excel workbook which consists of many sheets filled with formulas, graphs, etc. One of the sheets (call it "Master") in this workbook is empty, and on that sheet one puts data, that is used in all the other sheets in this workbook. On Master I have put a simple button that is connected to a macro which does a lot of things.

WHAT I WANT:
I do not want to work on the above worksheet, I want to make a copy of it on which I work and save this copy with a different name in a different location. But when I open the original, there should be a button that is always connected to the macro.

WHAT I HAVE DONE SO FAR:
I have the above file, there is the button connected to the macro. When I click the button the program runs and it fills in the data in the file and processes it. At the end I do "SaveAs" and save the copy with all processed data, I also delete the button on that copy before I save it.

PROBLEM:
When I "Save As", the original is closed as well, and next time I open it there is no data in Master sheet, just as I wanted. BUT the button is no longer connected to the macro! I have to go in and assign the macro again.

Unfortunately, I cannot upload the file here. But hopefully my description is granular enough to make someone come up with a suggestion how to keep the button connected to the macro.

Thanks in advance for your help!

[kiyiya]

Make sure you save the original file after you assign the macro to the button. You are probably forcing a close on the oringial file after assigning the macro and the "Save As" copy is where the macro is "bound" to the button that you delete. Therefore, no macro is not bound to the original file anymore.

[Peskarik]

Hi, kiyiya

I will try your suggestion. Though i think I've saved the original file after assignment.
I just tested "SaveAs" method on a simple file with a button and it works: the copy is saved and the original is in correct form and the macro is still assigned.

Doing these things I wondered if I can "SaveAs" a workbook, saving everything except VBA Module of the original file.

[mdmackillop]

Why not save your code as an Add-In (XLA file)

[Peskarik]

Why not save your code as an Add-In (XLA file)

Thank you for the suggestion! Did not know something like that could be done. I will look into that. Are there any helpfiles that describe how this is done?

[mdmackillop]

Look at this article (http://vbaexpress.com/forum/showthread.php?t=10855)