rjplante
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 558
- Office Version
- 365
- Platform
- Windows
I have a spreadsheet that the user will enter a number in a cell, which triggers a macro to search the directory and find the parent folder and then place the file path in a designated cell (CU1). When the user selects the save button on the screen, I have the macro check a cell to see if the file has been saved before, and if not, it opens a SaveAs dialogue box and the file name is entered into the filename entry field, and the location has been selected based on the information in cell CU1. Cell CL1 contains the file name that should be used to save the file. The problem I am having is that when the macro runs, the SaveAs window appears as desired, the fiile name entry field is filled in with the data from cell CL1, the correct file path is listed at the top of the dialogue box which is the path from CU1, the correct file format (.xlsm - for macro enabled workbook) is selected below the file name entry field, but when the user selects the Save button at the bottom of the dialogue box, the file is not saved. The rest of the macro runs just fine, but I cannot get the SaveAs part to run. My code appears below. What am I doing wrong?
VBA Code:
Sub SAVE_AS_FILE()
Dim FileFullName As Variant
Dim FileFilter As String
Dim FileInitial As String
FileInitial = Sheets("Block Tracking Multiple").Range("CU1").Value & Sheets("Block Tracking Multiple").Range("CL1").Value
FileFilter = "Excel Workbook (*.xlsm), *.xlsm"
' Get location and filename to be used for saving
If Sheets("Block Tracking Multiple").Range("I1").Value = "" Then
MsgBox "A job number has not been entered." & vbCrLf & "Enter a job number to continue."
Sheets("Block Tracking Multiple").Range("I1").Select
Exit Sub
Else
If Sheets("Block Tracking Multiple").Range("DA1").Value = "" Then
FileFullName = Application.GetSaveAsFilename(InitialFileName:=FileInitial, FileFilter:=FileFilter)
Sheets("Block Tracking Multiple").Range("DA1").Value = "YES"
If FileFullName = False Then
Sheets("Block Tracking Multiple").Range("DA1").ClearContents
End If
Else
ThisWorkbook.Save
End If
End If
End Sub