SaveAs Help

[Dowsey1977]

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]

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]

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]

Excellent...many thanks for you help!