BeforeSave and SaveAs to either save at predefined folder or let the user choose

Hi all,

I'm trying to create a macro in order to aks the user whenever he wants to save the workbook whether he wants to either save the active workbook at a predefined folder (predefined by macro) or to name the location as usual when pressing saveas.

I had some trouble with the beforesave-procedure creating an infinitive loop, but that was solved by using the search function of this forum. My code now looks like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo ErrorHandler
Application.EnableEvents = False
Cancel = True
Adress = "C:\TEM\test" & Range("b2").Value & ".xls"
Answer = MsgBox("Save at predefined location?", vbYesNoCancel, "Save")
If Answer = vbYes Then
ThisWorkbook.SaveAs Filename:=Adress
MsgBox "File saved in " & Adress
ElseIf Answer = vbCancel Then
MsgBox "File not saved!"
End If

ErrorExit:
Application.EnableEvents = True
Exit Sub

ErrorHandler:
MsgBox "File not saved!"
Resume ErrorExit

End Sub

The only problem I have now is that I can't get the usual saveas dialog running.
The idea is to save at a predefined location when vbyes is chosen, to abort saving when vbcancel is picked and to get the saveas dialog when vbno is taken.
I assume that if I set <Cancel = false> in an <elseif answer=vbno> it will just save the workbook at the current location, without the user beeing able to chose where).

Additionally, maybe someone can explain why I need the ErrorHandler. I tried to trigger it, but whatever button I press, the msgbox never showed (I know because I added the other "file not saved" msgbox later)...

Thx for any help in advance...

Greetz - Snyder

In case this post exists twice now, please remove one... had some network issues and am sorry for that case...

Snyder,

Try using the GetSaveAsFilename method. You can find a fairly good description and example of this in the VBA editor help files.

But essentially, it'll look something like this (untested):

Please Login or Register  to view this content.

Hi Ryan,

thx for the help, though my problem was solved by a collegue at work today. To share my revelations with everyone, there is a command to prompt the saveas UI:

Application.Dialogs(xlDialogSaveAs).Show

I just had to enter that with an if answer=vbno then line and all worked fine...

Code now looks like this:

Please Login or Register  to view this content.

So, anyhow, thx again for the effort, but as far as noone wants to explain me the part with that errorhandler anymore this topic is solved...

Greetz - Snyder