PDA

View Full Version : [SOLVED] SaveAs Help



Dowsey1977
08-08-2005, 09:59 AM
I'm using the below code to bring up a message box which gives the user the option of Yes, No and Cancel. What I want to happen is...
1. User clicks Yes = save file
2. User clicks No = SaveAs dialog box
3. Cancel = Cancel save

I can manage 1 & 3 but am stuck on number 2.


Message = MsgBox("This will save the file using the current " & _
"filename. Do you want to continue?" & vbCrLf & vbCrLf & _
"Current Filename and path is - " & " " & _
ActiveWorkbook.FullName & vbCrLf & vbCrLf & _
"Click 'Yes' to use this filename and path, 'No' " & _
"to change the filename and/or path or 'Cancel' to cancel " & _
"the save", vbYesNoCancel + vbQuestion, "Do you want to save?")
If Message = vbYes Then
ActiveWorkbook.Save
Else
If Message = vbNo Then
Name = Application.GetSaveAsFilename
If Name <> "False" Then
ActiveWorkbook.SaveAs (Name)
Else
If Name = "False" Then
End If
End If
If Message = vbCancel Then
End If
End If
End If
End Sub


So, if the user clicks no then the SaveAs dialog box appears, I can do that, and I can get it save using

Name = Application.GetSaveAsFile
ActiveWorkbook.SaveAs (Name)

However, I have added in the other rows in an attempt to get the save to be cancelled if the user then presses 'Cancel' in the SaveAs dialog box, but saves with Name if they click Ok....


Any ideas......

Norie
08-08-2005, 10:23 AM
If you press Cancel the value returned is the boolean value False.

Dim varName
Message = MsgBox("This will save the file using the current " & _
"filename. Do you want to continue?" & vbCrLf & vbCrLf & _
"Current Filename and path is - " & " " & _
ActiveWorkbook.FullName & vbCrLf & vbCrLf & _
"Click 'Yes' to use this filename and path, 'No' " & _
"to change the filename and/or path or 'Cancel' to cancel " & _
"the save", vbYesNoCancel + vbQuestion, "Do you want to save?")
If Message = vbYes Then
ActiveWorkbook.Save
Else
If Message = vbNo Then
varName = Application.GetSaveAsFilename
If varName <> False Then
ActiveWorkbook.SaveAs (varName)
End If
End If
End If

TonyJollans
08-08-2005, 12:51 PM
There is no need to have to handle the SaveAs dialog yourself at all - just let Excel do it. Instead of using GetSaveAsFileName, invoke the Dialog ...


Message = MsgBox("This will save the file using the current " & _
"filename. Do you want to continue?" & vbCrLf & vbCrLf & _
"Current Filename and path is - " & " " & _
ActiveWorkbook.FullName & vbCrLf & vbCrLf & _
"Click 'Yes' to use this filename and path, 'No' " & _
"to change the filename and/or path or 'Cancel' to cancel " & _
"the save", vbYesNoCancel + vbQuestion, "Do you want to save?")
Select Case Message
Case vbYes: ActiveWorkbook.Save
Case vbNo: Application.Dialogs(xlDialogSaveAs).Show
Case vbCancel: ' Do nothing
End Select

Dowsey1977
08-09-2005, 01:08 AM
Excellent...many thanks for you help!