A button to save workbook with file name from K1 cell

[danield_sk]

Hello All,

I have a sheet where users will fill in some values and print it. It is a template and print form substitution. I would like to have a button on the sheet that will save the whole workbook as an xls file with the file name from a value stored in the K1 cell.
For instance, if the K1 has the 'abc123' value in it, and the user clicks on the ‘save’ button, the workbook should be saved as abc123.xls. Next, I would like to have a ‘clear’ button, and when user clicks on the ‘clear’ button, all defined (unlocked and unprotected) cell content will be removed (sheet reset). How can I do it?

Thank you,

Danield_sk

[jasoncw]

For your first issue, create a command button, and copy the following code to the click event procedure (assuming the button name is CommandButton1):

Please Login or Register  to view this content.

For the second issue, my suggestion would be to first unprotect the sheet. Next, select all cells that are unlocked, and define a name for these (e.g. "Unlocked_Cells"). One way to do this is go to Insert > Name > Define on the menu. After naming this range, copy the following code to the button's click event procedure (assuming the button name is CommandButton2):

Please Login or Register  to view this content.

HTH

Jason

[danield_sk]

Hello Jason,

Thank you very much for your advice. I ended up using this for saving current workbook:

Please Login or Register  to view this content.

And that works as I wanted - Thank you!

However, the 'clear' function did not work. I was getting an error like "Improper use of Me function". I ended up recording a macro where I manually selected all cells I wanted to remove a content from and then hitting 'del' key. It works, but if you have a more elegant solution, please let me know.

Again, I greatly appreciate your time and your help.

Daniel

[jasoncw]

Hi, Daniel.

Glad you got the first issue working. As for the second, did you include the code in the worksheet module that houses the command button? Or did you include it as part of a general module? The use of "Me" in the code I posted reflects the sheet to which the code is attached. So it cannot be used in a general module. To fix this, you could simple substitute the sheet name for Me:

Please Login or Register  to view this content.

HTH

Jason

[danield_sk]

Jason,

I've just realized that I should problably check what characters will users type in K1 cell (user for filename). How should I ensure that users will use only alphanumberic characters?

Thank you for your time.

Daniel

[jasoncw]

Hi, Daniel. I just found this on another message board:

In cell K1, select Data > Validation from the menu. In the Allow box, select Custom, then copy the following formula in the Formula box:

Please Login or Register  to view this content.

This will ensure that only alphanumeric characters are entered in the cell. If you want to add a message box if an invalid character is entered, you would do this on the Error Alert tab of data validation.

HTH

Jason