Hello there, this is my first post so please be patient with me first let me explain what I'm trying to do; I've designed an Invoice template wich I want to:
1- Upon Open :
Auto Increment cells "E5" and "D9" *(Invoice number, and Ref code ) and after doing that , auto-save itself
NOTE: * don't need string name or location paths at this point, just save (overwrite) itself with it's own name in it's own location.
2- Before Save:
Once the invoice is filled, and the user clicks "save" or "save as", excel must sugest both the name and location where the file needs to be saved.
NOTE2: *the invoice name should be a combination of "E5" (Invoice#) and "D12" (Client name).
e. g. Invoice # is 1000001 and client name is Amy Moore,
then the new filename should be "1000001Amy Moore.xls"
and location must be like C:\Documents and Settings\Company name\Invoices\
HERE IS MY VB CODE:
Private Sub Workbook_Open()
[E5] = [E5] + 1
[D9] = [D9] + 1
With ActiveWorkbook
ActiveWorkbook.Save
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Sub SaveAsNameInCells()
Dim sFileName As String
Const sPath As String = "C:\Documents and Settings\Company name\Invoices\"
On Error Resume Next
With ActiveWorkbook
Sheets(1).Name = sFileName
sFileName = Range("E5").Value & Range("D12").Value
End With
Application.EnableEvents = False
If Len(Dir(sPath)) = 0 Then MkDir sPath
ChDir sPath
'Show the open dialog and pass the selected file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")
'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
exit_handler:
MsgBox "The required cells are empty", vbCritical, "Input required"
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Call SaveAsNameInCells
Application.EnableEvents = True
End Sub
So far the ONLY thing Not working in VB code is auto-save upon open, wich is really important in order to keep the invoice number secuence,
I just want the template to save itself each time "E5" increments, that way every time user opens it the new invoice# its up to date. Help!
1- Upon Open :
Auto Increment cells "E5" and "D9" *(Invoice number, and Ref code ) and after doing that , auto-save itself
NOTE: * don't need string name or location paths at this point, just save (overwrite) itself with it's own name in it's own location.
2- Before Save:
Once the invoice is filled, and the user clicks "save" or "save as", excel must sugest both the name and location where the file needs to be saved.
NOTE2: *the invoice name should be a combination of "E5" (Invoice#) and "D12" (Client name).
e. g. Invoice # is 1000001 and client name is Amy Moore,
then the new filename should be "1000001Amy Moore.xls"
and location must be like C:\Documents and Settings\Company name\Invoices\
HERE IS MY VB CODE:
Private Sub Workbook_Open()
[E5] = [E5] + 1
[D9] = [D9] + 1
With ActiveWorkbook
ActiveWorkbook.Save
End With
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Sub SaveAsNameInCells()
Dim sFileName As String
Const sPath As String = "C:\Documents and Settings\Company name\Invoices\"
On Error Resume Next
With ActiveWorkbook
Sheets(1).Name = sFileName
sFileName = Range("E5").Value & Range("D12").Value
End With
Application.EnableEvents = False
If Len(Dir(sPath)) = 0 Then MkDir sPath
ChDir sPath
'Show the open dialog and pass the selected file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")
'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
exit_handler:
MsgBox "The required cells are empty", vbCritical, "Input required"
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Call SaveAsNameInCells
Application.EnableEvents = True
End Sub
So far the ONLY thing Not working in VB code is auto-save upon open, wich is really important in order to keep the invoice number secuence,
I just want the template to save itself each time "E5" increments, that way every time user opens it the new invoice# its up to date. Help!