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):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim varUserDefined As Variant
    Dim strMyFileNameWithPath As String   
    
    strMyFileNameWithPath = "replace this text with your pre-defined filepath"

    On Error Resume Next
    Select Case MsgBox("Save?", vbYesNoCancel, "Save")
        Case vbYes
            ActiveWorkbook.SaveAs Filename:=strMyFileNameWithPath
        Case vbNo
            varUserDefined = Application.GetSaveAsFilename("Desired File Name", "Excel Files (*.xls), *.xls", , _
                "Choose Desired File Directory")
            If varUserDefined <> False Then
                ActiveWorkbook.SaveAs Filename:=varUserDefined
            Else
                Cancel = True
            End If
        Case vbCancel
            Cancel = True
    End Select
End Sub

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:

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")
  Select Case Answer
    Case vbYes
      ThisWorkbook.SaveAs Filename:=Adress
      MsgBox "File saved in " & Adress
    Case vbCancel
      MsgBox "File not saved!"
    Case vbno
      Application.Dialogs(xlDialogSaveAs).Show
  End Select

  ErrorExit:
    Application.EnableEvents = True
    Exit Sub

  ErrorHandler:
    MsgBox "File not saved!"
    Resume ErrorExit

End Sub

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