Create a Command Button to "Save As" and "Close" an Excel Workbook

[thedunna]

Greetings everyone,

I have, what I thought should be, a rather simple project. After two days of fumbling around, I need help.

I have a workbook that I use over and over again. What I would like to do is...after entering all the data, save the workbook in a designated folder on my desktop as a file name (entered on sheet 1 in cell K3), and then simultaneously close the workbook. When the workbook is opened for the next event it will be blank and ready for new data. Sort of like a template, but not a template in the true sense. It would be rare for the file name to already exist, but if it did, Maybe a prompt to overwrite might be helpful.

Thanks in advance for any help. This forum has been a tremendous resource for me in the past. The users are knowledgeable and friendly.

Jim

[Jakobshavn]

See the attached.

[thedunna]

Jakobshavn, thanks so much for your quick reply. I used the code but it is telling me it cannot be saved as a macro-enabled workbook. Is there one piece missing so that it saves as a .xlsm?

[Jakobshavn]

Make sure you have updated the filepath in the VBA to point to a folder on your desktop.

[thedunna]

I think the path is right. Popup box says "The following features cannot be saved in Macro-free Workbooks: .VB Project To save a file with these features, click No and then choose Macro-enabled file type in the file type list. To continue to save as a Macro-free workbook click Yes.

Did I insert something somewhere that I need to remove? .VB Project?

[Jakobshavn]

I get your error message if K3 contains:

Book1.xlsx
rather than
Book1.xlsm


check cell K3

[thedunna]

I seem to have it about 99% working. The earlier problem was that I never did save my original workbook as .xlsm. Once I did that things started working. One final glitch...the command button closes and saves the file okay, but excel never fully shuts down. The grey background of excel is still on the screen until I "x" close it. Also, as a final request. Is there a function that will disable the command button in the saved copy? Probably not, but I just didn't want the user to inadvertently save & close the saved copy over and over again. Thanks again for all your help, I'v accomplished more in the past couple of hours than the last two days.

[Jakobshavn]

The first issue can be addressed by replacing:
ThisWorkbook.Close
with:
Application.Quit

in the button code.

I don't know how to disable the button the saved version

[thedunna]

Thank you so much for your help! It's working perfectly now. Just a quick question for my own education. When I add a field, formatting a few cells as currency ($8 for example) it returns an error when I hit the command button. Is there an easy way around that or should I just leave it alone? Not a big deal really, just for looks... Thanks again!!!

[Jakobshavn]

Very odd..................I can't reproduce you problem.