VBA - Save as certain filename when "save" or "save as" is pressed

VBA - Save as certain filename when "save" or "save as" is pressed

Hi all,

I'm working on a file that needs to be saved as a certain filename (ex. John12week5.xls) in a location chosen by the user. I'm having some issues in "idiot-proofing" the operation.

I have developed code to complete the save alright that can be run off of a macro button. However I'd like to make it run when "save as" or "save" is pressed in the toolbar so the file is always properly saved.

If I try putting the macro's code directly into the workbook on the "before save" event (see code 1 below) but receive the following error
"Compile error, can't assign to read-only property"

I also tried calling the function I used to save in the workbook (see code 2 below) but that had more issues. It would go through fine but when I went to save with a strict filename, it initiates the "beforesave" event and starts the process again.

I'm using 2003 if that makes any difference. Any help is greatly appreciated.

Re: VBA - Save as certain filename when "save" or "save as" is pressed

I don't like to bump my own thread but with over 200 views doesn't someone have any insight? Somewhere I could start looking or a similar program that could be manipulated to fit my application?

Re: VBA - Save as certain filename when "save" or "save as" is pressed

As you already are aware of, if you create a button to run your code, you can do what you are trying to do.

The problem here is that the "Save" and "Save As" options that are native to Excel, are meant to do exactly what they do. You are wanting to customize those native functions.

The only way that I know that you can accomplish what you want is for you to create you own Ribbon and use your custom Ribbon in place of the initial, default ribbon from Excel. When you create you own ribbon, you can include any options you want.

Just my thoughts. Hope it helps get you going with your project.

Re: VBA - Save as certain filename when "save" or "save as" is pressed

Interesting thought, something that hadn't crossed my mind. I'm using 2003 so there isn't a ribbon per say but I'll take a look into if that kind of thing is available for my version.

Thanks

Re: VBA - Save as certain filename when "save" or "save as" is pressed

Sorry that I made the assumption that you were working with a version of Access that uses the ribbons. You can design a custom menu and the same thing would apply.

Re: VBA - Save as certain filename when "save" or "save as" is pressed

it's been a while since i used excel 2003, but couldn't you create your own toolbar buttons in that version? i vaguely remember making some, and even altering their design..
if this is true indeed, the solution could be simply getting rid of the native 'save (as)' buttons, and using your own custom ones with macro assigned.
l