Run time error 1004 for saving excel file (VBA required)

Question

I was wondering if anyone knows how to use vba to save a .txt file that is opened in excel?

I have tried writing a coding with a UserForm, but it is giving me errors.

I was wondering if it is possible to give user the option to save it at his/her favourite spot, and also his/her favorite name?

 Public Sub CommandButton1_Click()
 Dim YesOrNoAnswerToMessageBox As String
 Dim QuestionToMessageBox As String
 Dim CurrentFile As String

 QuestionToMessageBox = "Do you want to save?"

YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Save file")

If YesOrNoAnswerToMessageBox = vbNo Then
     Unload Me 'Cancellation command
Else
CurrentFile = ThisWorkbook.FullName
ActiveWorkbook.SaveAs "C:\myfile.xls", FileFormat:=52
Workbooks.Open CurrentFile
End If
End Sub

Answer 1

The error is because your file extension (xls) doesn't match your file type (OpenXMLWorkbookMacroEnabled). You would need the xlsm extension.

Sub Command1Click()

    Dim lResp As Long
    Dim sCurrFile As String
    Dim sNewFile As String

    Const sPROMPT As String = "Do you want to save?"
    Const sFILTER As String = "*.xlsm, *.xlsm"

    lResp = MsgBox(sPROMPT, vbYesNo, "Save File")

    If lResp = vbYes Then
        sCurrFile = ActiveWorkbook.FullName 'save current file name
        sNewFile = Application.GetSaveAsFilename(, sFILTER) 'get new file name
        If sNewFile <> "False" Then 'user didn't cancel
            ActiveWorkbook.SaveAs sNewFile, xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.Close False 'close new file
            Workbooks.Open sCurrFile 'open previous text file
        End If
    Else
        Unload Me
    End If

End Sub

Answer 2

I'm unsure why you're using Workbooks.Open after ActiveWorkbook.SaveAs. If the workbook is already open, isn't this unnecessary?

Anyway, to prompt the user for a save location try modifying the following as you require:

Sub DoooooooooooooooooooIt()

    Dim fd As FileDialog

    Set fd = Application.FileDialog(msoFileDialogSaveAs)

    With fd
        .Show
        If .SelectedItems.Count > 0 Then
            Debug.Print .SelectedItems(1)
        End If
    End With

End Sub