Robinsyn,
This is part of VBA that I have tested and used. 99% of it was contributed my many people, mostly from MrExcel (I don't know how to create code myself.). If it doesn't work, I can send you the entire code in case I left something out by mistake. This is the first time that I've been able to give back!! And, ironically, just a few hours ago you helped me!
SteveC
-------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' frm RockyH031202
Dim Path As String ' path of current worksheet
Dim ThisFileNew As String ' new file name including path
Dim Resp As Integer ' user response to overwrite query
Dim fname As String ' to DEL old file (frm NateO031202 MrExcel)
fname = ActiveWorkbook.FullName ' to DEL old file (frm NateO031202 MrExcel)
If Not Intersect(Target(1), Range("N2")) Is Nothing Then
With Application
.EnableEvents = False
.DisplayAlerts = False
End With
On Error Resume Next
' Set cell contents (file name) to upper case --In case you have a letter in invoice #
Target.Value = UCase(Target.Text)
' Get current path (empty if workbook has never been saved)
Path = ThisWorkbook.Path
If Not Path = "" Then Path = Path & ""
ThisFileNew = Path & Target.Text & ".xls"
Resp = vbOK
' Check for existing file of same name and, if present, ask whether to overwrite
' (frm Damon Ostrander 08220 2-22:38 mrexcel)
If Dir(ThisWorkbook.Path & "\" & Target.Text & ".xls") <> "" Then
Resp = MsgBox("This file already exists. Overwrite? ", vbExclamation + vbOKCancel)
End If
' Save the workbook if file does not exist, or if user wants to overwrite it
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Kill fname ' to DEL old file (frm NateO 031202 mrexcel)
Else
Resp = MsgBox("You will need to rename this file manually", vbInformation)
End If
On Error GoTo 0
With Application
.DisplayAlerts = True
.EnableEvents = True
End With
End If
End Sub