Problem executing macro on first run after opening workbook

[zott69]

Hi

This is my first post, and I'm quite a newbie to VB so please be gentle.

I'm having an issue whereby the code that I've written (with a little help) to save the existing file as new, which checks to see if a file already exists with the same name, doesn't run as expected on first run after opening the workbook.

In the code, I try to bypass the automatic prompt for "file already exists" to give the user the option to change the filename and so the macro doesn't break. But on first run, the automatic prompt comes up, meaning when you choose No/Cancel the error code '1004' comes up and breaks the macro.

Strangley, the macro works fine the second time you run it.

Sub save_it_as_new()

    dToday = Format(DateTime.Now, "yyyymmdd")
    x = MsgBox("This will save as a new file with today's date", vbOKCancel, "")
    f = "name_of_file " & dToday & ".xlsm"

[COLOR=#ff0000]'''on first run the code follows Case 1, on second run the code follows (correctly) Case Else[/COLOR]
    Select Case x
    Case 1
        If Not FileExist((f)) Then [COLOR=#008000]'file doesn't exist[/COLOR]
        
        ChDrive "C:\"
        ChDir "C:\location\of\file\"


        ActiveWorkbook.SaveAs f
        
        Else [COLOR=#008000]'file does exist[/COLOR]
            z = MsgBox("Filename already exists - overwrite?", vbYesNoCancel, "")


            Select Case z
            Case 6 [COLOR=#008000]'user pressed Yes[/COLOR]
                Application.DisplayAlerts = False
                 ChDrive "C:\"
                 ChDir "C:\location\of\file\"


                 ActiveWorkbook.SaveAs f
                 Application.DisplayAlerts = True
            Case 7 'user pressed No
                 Application.GetSaveAsFilename
                
            Case 2 [COLOR=#008000]'user pressed Cancel[/COLOR]
                 Exit Sub
               End Select
         End If
        
    Case 2 [COLOR=#008000]'user pressed Cancel[/COLOR]
        Exit Sub
        
    End Select
     
End Sub


Function FileExist(FilePath As String) As Boolean
[COLOR=#008000]'PURPOSE: Test to see if a file exists or not[/COLOR]
[COLOR=#008000]'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault[/COLOR]
[COLOR=#008000]'RESOURCE: http://www.rondebruin.nl/win/s9/win003.htm[/COLOR]


Dim TestStr As String


  On Error Resume Next
    TestStr = Dir(FilePath)
  On Error GoTo 0


[COLOR=#008000]'Determine if File exists[/COLOR]
  If TestStr = "" Then
    FileExist = False
  Else
    FileExist = True
  End If


End Function