VBA to force a file to be named using a certain format

[austin397]

I would like to make a way so that when the user saves it will force them to save the file a certain way,

Ex. "XX-xxXXxx-x BOM" Where XX equals letters and xx equals numbers.

 

[Leith Ross]

Hello austin397,

Is the user entering the file name before saving the file or will the file name be generated automatically when the user saves the file?

 

[Tim_Excel_]

I don't reckon you can force a file to be named a certain way once in the save as window, but maybe you could work something out with input boxes.

val1 = InputBox("Write 2 letters")
val2 = InputBox("Write 2 numbers followed by two letters followed by 2 numbers")
val3 = InputBox("Write 2 letters")

And then save it as ""&val1&"-"&val2&"-"&val3&" BOM.filetype"
You could then add some checks to determine if the user wrote exactly the amount of symbols which is required.
Of course, this is not a very elegant way, so if someone else has a better option..

 

[austin397]

The user will be entering the file name. I only want them to be forced to name it following a specific format.

Thank you for your reply!

 

[Leith Ross]

Hello austin397,

Where is the user entering the file name: Worksheet, VBA UserForm, SaveAs Dialog?

 

[austin397]

Either on a VBA UserForm or the save as Dialogue box is fine. If it's in the worksheet the user will easily forget to do it.

Thanks!

 

[Leith Ross]

Hello austin397,

A VBA UserForm would be best. It gives you the most control and flexibility with data entry.

I take it that you don't already have a UserForm in your workbook. Would you like me to create a sample workbook with a UserForm to do this?

I can post a link to the sample workbook since I can not post one here.

 

[austin397]

If you can do that, that would be great! If you don't want to go through the trouble of that, just a sample VBA code should do.

Thanks!

 

[Leith Ross]

Hello austin397,

It isn't any trouble to do. I would need the UserForm anyway to write the VBA code.

 

[Leith Ross]

Hello austin397,

I have the UserForm and it's code complete. But, I have a question. Will this code be added to your Personal.xls workbook?

If not then will this macro be saving another open workbook?