I found a work around for this that involved removing the
ActiveWorkbook.Close at the end and adding On Error Resume Next to the
beginning of the macro. This doesn't automatically close the workbook
anymore but it did cure the problem with removing the run time dialog
box from popping up. All the user has to do is answer yes or no to
the Windows default for writing over the file and the macro finishes.
Consider my problem fixed.
On May 13, 4:13 pm, Opeth121 <hovio...@yahoo.com> wrote:
> 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
