Folks,
I have a Button control at the top of my worksheet which, when clicked, will save the file with the ID included in the file name (ID sits in cell D9).
My problem is that if the file already exists, a Microsoft window pops up and says "A file named X already exists. Do you want to replace it?"
If I say yes, no problem.
If I say no, a window comes up that says
"Runtime error '1004'. Cannot access file"
and gives user the choice of End, or Debug.
How can I get VBA to gracefully allow a user to say "No" (no, I don't want to replace the file)?
This is my code:
Public Sub SaveAsA1()
If Len(Dir("C:\ifed", vbDirectory)) = 0 Then
MkDir "c:\ifed"
End If
ThisFile = Range("D9").Value
ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ " & [ThisFile] & ".xlsm"
'ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ThisFile.xlsm"
End Sub
I have a Button control at the top of my worksheet which, when clicked, will save the file with the ID included in the file name (ID sits in cell D9).
My problem is that if the file already exists, a Microsoft window pops up and says "A file named X already exists. Do you want to replace it?"
If I say yes, no problem.
If I say no, a window comes up that says
"Runtime error '1004'. Cannot access file"
and gives user the choice of End, or Debug.
How can I get VBA to gracefully allow a user to say "No" (no, I don't want to replace the file)?
This is my code:
Public Sub SaveAsA1()
If Len(Dir("C:\ifed", vbDirectory)) = 0 Then
MkDir "c:\ifed"
End If
ThisFile = Range("D9").Value
ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ " & [ThisFile] & ".xlsm"
'ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ThisFile.xlsm"
End Sub