I have a vba macro which is attached to a button on my worksheet. When the button is pressed the intention is to save the file based on a number in cell E4 (which is uses for the file name). There are 2 checks I want to do before the file saves:-
Check cell E4 has a valid job number in it
Check the destination directory already exists
The sequence I'm trying to achieve is to check if E4 has a number in it (this is a job number), and display a message if it doesn't. If there is a number in cell E4 then a message box pops up asking if the user wants to save the file. If the user says yes then a check is done that a folder already exists (which is called the same as the number in E4), If there is a valid folder then the file is saved with a filename constructed from the number in E4 and some text. I'm getting a compile error regarding End If so I'm assuming my syntax isn't right but seems to look logically right to me...
Can anyone point out the error of my ways please?
Check cell E4 has a valid job number in it
Check the destination directory already exists
The sequence I'm trying to achieve is to check if E4 has a number in it (this is a job number), and display a message if it doesn't. If there is a number in cell E4 then a message box pops up asking if the user wants to save the file. If the user says yes then a check is done that a folder already exists (which is called the same as the number in E4), If there is a valid folder then the file is saved with a filename constructed from the number in E4 and some text. I'm getting a compile error regarding End If so I'm assuming my syntax isn't right but seems to look logically right to me...
VBA Code:
Sub SaveAsDIF()
On Error Resume Next
Dim JRef As String
Dim FPath As String
Dim FName As String
Dim Answer As Integer
Dim myCell As Range
Dim strFolderName As String
Dim strFolderExists As String
Set myCell = Sheets("DIF").Range("E4")
strFolderName = "Y:\" & Sheets("DIF").Range("E4").Text
strFolderExists = DIR(strFolderName, vbDirectory)
If IsEmpty(myCell) Then
MsgBox "FSL Job No needs inserting"
Else
Answer = MsgBox("Are you sure you want to save this DIF?", vbYesNo + vbQuestion + vbDefaultButton2, "Save DIF")
Else
If strFolderExists = "" Then
MsgBox "The selected folder doesn't exist", vbOKOnly, "Folder Error"
Else
If Answer = vbYes Then
JRef = "J"
FPath = "Y:\" & Sheets("DIF").Range("E4").Text & "\" & "Site Data"
FName = Sheets("DIF").Range("E4").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & JRef & FName & " - DIF", FileFormat:=52
Else
Answer = MsgBox("This DIF is unsaved", vbQuestion + vbOKOnly, "Save DIF")
End If
End Sub
Can anyone point out the error of my ways please?