Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check File Name before Save Event
I am distributing a file to clients and they are returning it to me. want to send it out with the name "workbook1". When they return it t me, I want to stop them from saving it under the same name. The cod below works given that the file is already named "workbook1". Howeve it also prevents the user from changing the name. Instead of using th Name property as the test, I need to have the user attempt to save th file and somehow pass the proposed name, that goes into the Save o Save as filename box, to a variable and use this as the test tha drives the Save Event. Thanks Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A Boolean) Dim FileName As String FileName = ThisWorkbook.Name If FileName = "Workbook1.xls" Then MsgBox ("You cannot save this file using this name?" & vbCr & _ "It is recommended that you save it using your name as a extension" & vbCr & _ "to this name(i.e. Workbook1JohnSmith)") Cancel = True End If End Su -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26427 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check File Name before Save Event
The following BeforeSave procedure keeps asking until the user enters a valid file
name or cancels: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim FileName As String If ThisWorkbook.Name = "Workbook1.xls" Then Do ' returns path and name FileName = Application.GetSaveAsFilename _ (Title:="Browse to a directory and enter a file name") ' check the name If Right(FileName, 13) = "Workbook1.xls" Then ' don't use Workbook1.xls MsgBox "That file name is not permitted." & vbCrLf & _ "Please select another file name.", vbExclamation, _ "Invalid File Name" ElseIf Len(Dir(FileName)) 0 Then ' duplicate name MsgBox "A file with that name already exists." & vbCrLf & _ "Please select another file name.", vbExclamation, _ "File Exists" ElseIf FileName = "False" Then Exit Do Else Exit Do End If Loop Cancel = True If FileName < "False" Then Application.EnableEvents = False ThisWorkbook.SaveAs FileName Application.EnableEvents = False End If End If End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ ExcelMonkey wrote: I am distributing a file to clients and they are returning it to me. I want to send it out with the name "workbook1". When they return it to me, I want to stop them from saving it under the same name. The code below works given that the file is already named "workbook1". However it also prevents the user from changing the name. Instead of using the Name property as the test, I need to have the user attempt to save the file and somehow pass the proposed name, that goes into the Save or Save as filename box, to a variable and use this as the test that drives the Save Event. Thanks Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileName As String FileName = ThisWorkbook.Name If FileName = "Workbook1.xls" Then MsgBox ("You cannot save this file using this name?" & vbCr & _ "It is recommended that you save it using your name as an extension" & vbCr & _ "to this name(i.e. Workbook1JohnSmith)") Cancel = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check folder event | Excel Programming | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming | |||
Change Event.....Spell Check | Excel Programming | |||
Help - Change Event triggered on File Save As | Excel Programming | |||
After save event | Excel Programming |