Call in Variable File Directory and Filename from Cell into Save As Prompt

Hi Everyone,

This is my first time posting in this forum (or any for that matter), but I have used the forum extensively and have really appreciated the value of the knowledge here. I really need some help that I'm sure will be simple, but I am very new to VBA so it's difficult for me to piece together.

I basically just want a macro that will activate every time the Save or SaveAs functions in Excel are pressed, that will open up the SaveAs Dialogue with a file name and file directory in Sheets("Input Sheet").Range("BN1"). The value in BN1 is a concatenated filename and directory that changes based on an order number and today's date (currently "13-MELLON INV\13-Trans Stmts\1st Qtr\02-FEB\1302 - 1111A Trans Statements.xls). I would like the "SaveAs" dialog to open up to "S:\13-MELLON INV\13-Trans Stmts\1st Qtr\02-FEB\", with "1302 - 1111A Trans Statements.xls" as the suggested filename to be saved. There is a chance the name will need to be modified on an occasional report, i.e. one that has an anomaly that needs to be checked might have the filename altered slightly, so I do need the dialog rather than just having the macro save it completely.

I have played around with some other code I have found online, which I will list below, but run into a couple of problems. One, because it is a BeforeSave event, it brings it up the way I would like, but then immediately comes up to save again, at which point I have to either cancel the second save or select to override the file the event just saved. I'd like the saveas from the event to be the only save. Second, I don't know if it's possible to make a normal Save require the SaveAs dialog as well, since these are important files, but if there is a way to do it (or disable normal Save altogether), I would also like to include that. Here is what I have:

Please Login or Register  to view this content.

Thanks for any help you can give me!