Patrick C. Simonds;205378 Wrote:
The code below is triggered by a command1 button on on UserForm1. It
saves
the document with a name derived by combining data in certain cells.
My
problem is if that document name already exists. I do not want the user
to
have the option of replacing the original file or renaming the current
file. I want a MsgBox which tells them that the file already exists.
Once
the MsgBox is dismissed I want UserForm1 to reappear so they can select
a
new date.
Code:
--------------------
Private Sub CommandButton1_Click()
Dim myRange As Range
Dim myDate As Range
Set myRange = Worksheets("Relief Board").Range("C3")
Set myDate = Worksheets("Relief Board").Range("C4")
Protection.unprotect_all_sheets
myRange.Value = ""
myDate.Value = Calendar1.Value
Protection.protect_all_sheets
ActiveWorkbook.SaveAs Filename:="P:\AA Exception\ " & Worksheets("Relief
Board").[B3].Value & ", " & Worksheets("Relief Board").[D3].Value &
"_Exception Sheet", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Unload StartNewDay
End Sub
--------------------
Hello Patrick,
You can use the Dir statement to test if a file exists. You can even
use wild card characters to match similar names.
Code:
--------------------
FileName = Dir("P:\AA Exception\MyFile.xls")
'If the file doesn't exist, FileName will be an empty string "".
If FileName < "" Then
MsgBox "File Exists. Please Use a Different Name."
UserForm1.Show
End If
--------------------
--
Leith Ross
Sincerely,
Leith Ross
'The Code Cage' (
http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=56428