Save File VBA code works perfectly....except.....

[jmk15315]

So with the help from others here, I have landed on the following code. It works perfectly, with the exception that it does not allow the user to choose the folder in which to save the file to. I thought I could figure it out, but everything I have tried, has failed. Hoping someone can point me in the correct direction.


Private Sub CommandButton2000_Click()

If Sheet2.Range("G20").Value = "NOT NEW" Then
GoTo 900
Else
End If

Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String

TempFilePath = ThisWorkbook.Path
Dim NewFileName As String
FileExtStr = ".xlsm": FileFormatNum = 5

Sheet2.Visible = True
NewFileName = Sheets("DATA FILE").Range("G11").Value & " - " & Sheets("DATA FILE").Range("G12").Value & " - " & Sheets("DATA FILE").Range("G22").Value
Sheet2.Visible = False

ActiveWorkbook.SaveAs TempFilePath & "\" & NewFileName & FileExtStr

Application.ScreenUpdating = True

Application.EnableEvents = True

MsgBox "Success - The file has been saved to: '" & TempFilePath & NewFileName & "'", vbInformation

Sheet2.Visible = True
Sheet2.Select
Range("G20").Select
ActiveCell.FormulaR1C1 = "NOT NEW"
Sheet2.Visible = False

900
With MeetingData
MeetingData.Height = 423
MeetingData.Width = 893
End With
Load MeetingData
MeetingData.MultiPage1.Value = 1
End Sub

 

[rlv01]

Perhaps replace this:

TempFilePath = ThisWorkbook.Path

with this:

       'File dialog folder select
       With Application.FileDialog(msoFileDialogFolderPicker)
           .InitialFileName = ThisWorkbook.Path
           .Filters.Clear
           .AllowMultiSelect = False
       
           'Use the Show method to display the FolderPicker dialog box and return the user's action.
           If .Show = -1 Then    'User picked one or more folder
               TempFilePath = .SelectedItems.Count
           Else    'The user pressed Cancel.
               MsgBox "User Cancel"
               Exit Sub
           End If
       End With

(Not tested)


(Tip: For future posts , you should try to use code tags like I did above when posting your code. It makes it easier to read.)

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com