Save as conflict resolution

[JG4life]

I have VB Code handling the save as function of a form. When the form already exists it comes up and asks if you want to overwrite it and if you say yes, it does, if you say no, it doesn't. however I have a mesage Box come up afterwards that lets you know that it was saved and where. However, that message box comes up even if you select no.

I am assuming it is an if statement to accomplish this but I am not really sure how to write it. I want it to say it wasn't saved if they click no and that it is saved if they click yes.



ActiveWorkbook.SaveAs Filename:="AR" + "_" + x + "_" + y, _
FileFormat:=xlNormal
Message = "Your Activity Report has been saved in your 'My Documents' folder."
MsgBox (Message)


Any help would be great!!

Sean

[Bob Phillips]

Dim sFilename

sFilename = "AR" + "_" + x + "_" + y
sFilename = Dir(sFilename)
If sFilename <> "" Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=sFilename, _
FileFormat:=xlNormal
Application.DisplayAlerts = True
Message = "Your Activity Report has been saved in your 'My Documents' folder."
MsgBox (Message)
Else
sFilename = "AR" + "_" + x + "_" + y
ActiveWorkbook.SaveAs Filename:=sFilename, _
FileFormat:=xlNormal
End If

[JG4life]

Thanks for the reply xld. However, there was other code I should have posted that controls where it put the filedepending on other information within the spreadsheet and it was just getting too difficult to code the different circumstances without changing alot of other code that was working fine. I found a slightly easier way of doing it. Below is the code that I used.



On error resume next 'hides the error

ActiveWorkbook.SaveAs Filename:="AR" + "_" + x + "_" + y, FileFormat:=xlNormal

If Err.Number = 1004 Then

MsgBox ("Your Activity Report was not saved")

Else

Message = "Your Activity Report has been saved in your 'My Documents' folder"
MsgBox (Message)

End If



I have never done error handling, I have always just put "on error resume next". Fought with "If err.Description =" and then realized it was "If err.Number =". Took me about an hour to figure that out and then realized that my code worked perfect an hour before that other than the Description vs Number LOL oh well, I get paid by the hour hehe!!

Thanks again!!