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.


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

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

I have to admit, I do not even know where to begin to integrate this into my
current code.


"Leith Ross" wrote in message
...

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

Patrick C. Simonds;205537 Wrote:
I have to admit, I do not even know where to begin to integrate this
into my
current code.


"Leith Ross" wrote in message
...

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 ExceptionMyFile.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' ('The Code Cage' (http://www.thecodecage.com/))

------------------------------------------------------------------------
Leith Ross's Profile:
'The Code Cage Forums - View Profile: Leith Ross'
(http://www.thecodecage.com/forumz/me...eith-ross.html)
View this thread: 'Prevent overwriting a file - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=56428)


Hello Patrick,

I added the code for you. Try it out and let me know if there are
problems.

Code:
--------------------

Private Sub CommandButton1_Click()

Dim FileName As String
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

CHeckFileName:
FileName = "P:\AA Exception\ " _
& Worksheets("ReliefBoard").[B3].Value _
& ", " & Worksheets("Relief Board").[D3].Value _
& "_Exception Sheet"

'If the file doesn't exist, FileName will be an empty string "".
If Dir(FileName) < "" Then
MsgBox "File Exists. Please Use a Different Name."
UserForm1.Show
GoTo CheckFileName
End If

ActiveWorkbook.SaveAs FileName:=FileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Unload StartNewDay

End Sub

--------------------


--
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

Thank you

I get a Subscript out of range error with the code below highlighted. I
thought the problem might be that the type of file was not indicated (in
this case xlsm) so I tried adding before the last quote mark but I still got
the same error.


FileName = "P:\AA Exception\ " _
& Worksheets("ReliefBoard").[B3].Value _
& ", " & Worksheets("Relief Board").[D3].Value _
& "_Exception Sheet"

Patrick C. Simonds;205750 Wrote:
Thank you

I get a Subscript out of range error with the code below highlighted.
I
thought the problem might be that the type of file was not indicated
(in
this case xlsm) so I tried adding before the last quote mark but I
still got
the same error.


FileName = "P:\AA Exception\ " _
& Worksheets("ReliefBoard").[B3].Value _
& ", " & Worksheets("Relief Board").[D3].Value _
& "_Exception Sheet"

Hello Patrick,

The only subscripts would be cells. Everything else is a fixed string.
Do the cells have the correct values? Are all the names of the
worksheets spelled correctly? If all that checks out OK, I really don't
know what the problem would be. I don't have Excel 2007 so I can't run
it even if you posted the workbook

I am sorry to have wasted your time on that last question. I have been so
wrapped up in this project I totally missed that ReliefBoard should have
been Relief Board (two words).


"Leith Ross" wrote in message
...

Patrick C. Simonds;205750 Wrote:
Thank you

I get a Subscript out of range error with the code below highlighted.
I
thought the problem might be that the type of file was not indicated
(in
this case xlsm) so I tried adding before the last quote mark but I
still got
the same error.


FileName = "P:\AA Exception\ " _
& Worksheets("ReliefBoard").[B3].Value _
& ", " & Worksheets("Relief Board").[D3].Value _
& "_Exception Sheet"

Hello Patrick,

The only subscripts would be cells. Everything else is a fixed string.
Do the cells have the correct values? Are all the names of the
worksheets spelled correctly? If all that checks out OK, I really don't
know what the problem would be. I don't have Excel 2007 so I can't run
it even if you posted the workbook
.


--
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

Patrick C. Simonds;205854 Wrote:
I am sorry to have wasted your time on that last question. I have been
so
wrapped up in this project I totally missed that ReliefBoard should
have
been Relief Board (two words).


"Leith Ross" wrote in message
...

Patrick C. Simonds;205750 Wrote:
Thank you

I get a Subscript out of range error with the code below
highlighted.
I
thought the problem might be that the type of file was not
indicated
(in
this case xlsm) so I tried adding before the last quote mark but I
still got
the same error.


FileName = "P:AA Exception " _
& Worksheets("ReliefBoard").[B3].Value _
& ", " & Worksheets("Relief Board").[D3].Value _
& "_Exception Sheet"

Hello Patrick,

The only subscripts would be cells. Everything else is a fixed
string.
Do the cells have the correct values? Are all the names of the
worksheets spelled correctly? If all that checks out OK, I really
don't
know what the problem would be. I don't have Excel 2007 so I can't
run
it even if you posted the workbook
.


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' ('The Code Cage' (http://www.thecodecage.com/))

------------------------------------------------------------------------
Leith Ross's Profile:
'The Code Cage Forums - View Profile: Leith Ross'
(http://www.thecodecage.com/forumz/me...eith-ross.html)
View this thread: 'Prevent overwriting a file - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=56428)


Hello Patrick,

I misspell words a lot when coding. My typing skills are my weakness,
Did that solve the problem?


--
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