I have a file which opens in one directory and when closed the user is presented with a different directory to save in.
The code attached lets me present the user with the proper name, allows you to click all of the right buttons but doesn’t save the file.
if I click on an existing name it will let ask me to replace the file.
Any thought would be greatly appreicated.
***********Form Code Starts Here***********
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sQ As String
ThisWorkbook.Saved = True
sQ = MsgBox(“Do you want to save this workbook ?”, vbYesNoCancel)
If sQ = vbCancel Then Cancel = True
If sQ = vbYes Then
If FSaveAs = False Then
Cancel = True
End If
End If
End Sub
*******Module Code Starts Here***********
Option Explicit
Function FSaveAs() As Boolean
Dim sFileName As String
Dim InitialDir As String
Dim SaveAsDialog As FileDialog
Dim FileDest As Variant
ChDrive “F”
ChDir “F:\Public\Training Reports”
sFileName = Sheet1.[b5]
Set SaveAsDialog = Application.FileDialog(msoFileDialogSaveAs)
FileDest = “F:\Public\Training Reports”
With SaveAsDialog
.InitialFileName = sFileName
.Show
End With
On Error GoTo FileError
FSaveAs = True
Exit Function
FileError:
MsgBox “You forgot to select a Subject Name for this report.”, vbRetryCancel
FSaveAs = False
End Function
********* All Code Ends Here*****************