Thank you in advance. I searched many sites and threads. I am most
likely missing something very easy but I am just lost at this point.
I have en Excel file that contains a "save" button. The button will
help the user execute an auto save to a folder along with a file name
(based on the invoice number the user enters). The Excel default
alerts will notify the user that the file exists (if they forget to
enter a new invoice) but then they will have to click on NO and then
on END once the run time error 1004 window comes up. This user is not
computer savvy at all and I wanted to create a seamless macro. The
user tends to get lost when clicking on too many things.
In a nutshell, I wanted to have the macro check to see if the file
exists, alert the user specifically that they have to enter a new
invoice number if they forgot, then exit the macro so they could enter
the new invoice and then click on the save button again. Here is the
code I have so far:
Function FileExists(ByVal FileName As String) As Boolean
FileExists = Len(Dir(FileName)) > 0
End Function
Public Sub testforfile()
Dim FileName As String
FileName = ActiveWorkbook.Worksheets(1).Range("AB6").Value
If FileExists(FileName) Then
If MsgBox("Invoice number already exists - please enter a new
Invoice number. ", vbOK) = vbOK Then Exit Sub
End If
End Sub
Sub Save_Invoice()
'Save_Invoice Macro
'Macro recorded 05/11/09 by Clyde Perrywinkle
Sheets("Service Invoice").Unprotect Password:="******************"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D3").Select
Application.CutCopyMode = False
Range("B2").Select
Sheets("Service Invoice").Protect Password:="****************"
If Len(Dir(ActiveWorkbook.Worksheets(1).Range("AB3").Value,
vbDirectory)) < 1 Then
MkDir ActiveWorkbook.Worksheets(1).Range("AB3").Value
End If
If Len(Dir(ActiveWorkbook.Worksheets(1).Range("AB4").Value,
vbDirectory)) < 1 Then
MkDir ActiveWorkbook.Worksheets(1).Range("AB4").Value
End If
FName = ActiveWorkbook.Worksheets(1).Range("AB5").Value
FPath = ActiveWorkbook.Worksheets(1).Range("AB4").Value
FSpec = FPath & FName
Application.Run "'Invoice - Service 5-09-09.xls'!testforfile"
ActiveWorkbook.SaveAs FileName:=FSpec
ActiveWorkbook.Close
End Sub
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
