GetSaveAsFilename problem in Excel

[gillwest]

Hi, I have written the following code but when it prompts the browser at GetSaveAsFilename, BOTH the Save and Cancel button Save the file. I have tried inserting a 'if vbsave then' command but then this creates both the save and the cancel button on the Browser to cancel. Please Help me find a way of getting the Save button to Save and the cancel button to Cancel..I am sure it is very simple but I am a learner in VBA !!

Gill

Private Sub Workbook_Open()
Dim MyFile, MyPath, MyName

mycheck = MsgBox("NOTE: The figures will not automatically update if you have opened this document from 'WIP'. To update the figures you MUST access the document through the Portal or the MReview Shortcut. Do you want to continue ? ", vbYesNo)

If mycheck = vbNo Then
ActiveWorkbook.Close
End If

myResp = MsgBox("If a C:\WIP folder exists, select 'YES' to save the file to that location. If you have not got a WIP folder on C:\ and would like to create one, also select 'YES'. (This will create the WIP folder and save the document there). To save elsewhere, select 'NO'.", vbYesNo)

If myResp = vbNo Then
filesavename = Application.GetSaveAsFilename("C:\Manual_MREVIEW_YYMM_Engagement", fileFilter:="Excel Files (*.xls), *.xls&quot
ActiveWorkbook.SaveAs
Exit Sub
End If

MyFile = Dir("C:\&quot
MyPath = "C:\WIP"
MyName = Dir(MyPath, vbDirectory)
If MyName <> &quot;&quot; Then
ActiveWorkbook.SaveAs (&quot;C:\WIP\Manual_MREVIEW_YYMM_Engagement&quot
Else
MkDir &quot;C:\WIP&quot;
filesavename = Application.GetSaveAsFilename(&quot;C:\WIP\Manual_MREVIEW_YYMM_Engagement&quot;, fileFilter:=&quot;Excel Files (*.xls), *.xls&quot
ActiveWorkbook.SaveAs (&quot;C:\WIP\Manual_MREVIEW_YYMM_Engagement&quot
End If

End Sub

 

[RobBroekhuis]

The GetSaveAsFilename method only retrieves the filename - it doesn't in any way save the file. Your code gets the filename, but then proceeds to save under the original filename. Try replacing it with:

filesavename = Application.GetSaveAsFilename(&quot;C:\WIP\Manual_MREVIEW_YYMM_Engagement&quot;, fileFilter:=&quot;Excel Files (*.xls), *.xls&quot
if filesavename<>&quot;False&quot; then 'user didn't hit cancel
ActiveWorkbook.SaveAs filesavename
end if

Rob