Excel VBA File save

Question

I am trying to set up an auto save function on an excel template so that the original template isnt over written.

Dim NameFile As String

'Gets the users username
UserName = Environ$("UserName")

'User is to input data type 
datat= Application.InputBox("Enter a Data Type", "Data Type")

    With Worksheets("Home")



    'Sets up auto filename with YearMonthDay - Username - Filename(From a specific Cell) - Data Type
    NameFile = Format(Date, "yyyymmd") & " - " & UserName & " - " & Range("A1") & " - " & datat & ".xlsm"
    End With
    'Sets up save location
    NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Desktop\" & NameFile, Filefilter:=" Excel (*.xlsm), *.xlsm")

    If NameFile = False Then
    'Tell user with a caution that the file has not been saved

    MsgBox "File not saved", vbCritical, "Caution"

    Exit Sub

    Else
    ThisWorkbook.SaveAs Filename:=NameFile

    MsgBox "File Saved"
    End If

When I dont save the file and click cancel i get the message box telling me that the file hasnt been save... which is what I want.

But when I do save the file with the given name I get a Run-Time error '13', Type Mismatch

What am I doing wrong?

Answer 1

You should Dim NameFile as Variant

That way it can hold either a Boolean or a String.