Autosave after cell increment

[kmendez]

Hello there, i'been posting this same question in almost every excell forum
without am accurate answer, I'm almost givin up!

I have this code for auto increment cell "E5" upon open an invoice template:

Private Sub Workbook_Open()

[E5] = [E5] + 1

End Sub


All I need its a piece of code that saves the file right after cell increment
without asking me (no "save as" window). just simply save itself , that way it will always keep the last number of the sequence.

It's that possible?

 

[JoeMo]

Try:

Private Sub Workbook_Open()

      [E5] = [E5] + 1
me.save

End Sub

 

[kmendez]

Thank you JoeMo,I realise it wasn't working because i have a "Workbook_BeforeSave" Sub that was triggering the "save as" window. It works now, but I had to change the "Workbook_BeforeSave" to "Workbook_BeforeClose" and I don't even know where to start. Let me explain for what I need the code for:

*When attempting to close the workbook excel asks me if i want to save the changes

1-If I hit "No" it just closes the workbook like it would normally do
2-If I hit "Yes" it will call "SaveAsNameInCells" Sub *(see VB code below)
3-If I hit "Cancel" it will just return to the workbook like it would normally do

HERE is the whole code (it's an Invoice template BTW)

Private Sub Workbook_Open()

[E5] = [E5] + 1

Me.Save

End Sub

Sub SaveAsNameInCells()

Dim sFileName As String

Const sPath As String = "C:\Documents and Settings\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
--------------------- Here is where I need your help-------------------------------

Private Sub Workbook_BeforeClose

?????????????????????


Too complicated?, asking too much? let me know please

 

[JoeMo]

Not sure I understand what you want. Can't you just have the workbook_beforeclose module call Sub SaveAsNameInCells module?

 

[kmendez]

I DID IT!


Private Sub Workbook_Open()

[E5] = [E5] + 1

[D9] = [D9] + 1

Me.Save

End Sub

Sub SaveAsNameInCells()

Dim sFileName As String

Const sPath As String = "C:\Documents and Settings\carolina\Mis documentos\Cuentas\In Models Management\Agencia\Administrativo\Facturacion\Fiscal\"
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_BeforeClose(Cancel As Boolean)
Cancel = True

Application.DisplayAlerts = False

Dim exit1 As Integer

exit1 = MsgBox(" In Models deseas salvar esta factura?", vbYesNoCancel, "Salir?")
Select Case exit1



Case vbYes
Call SaveAsNameInCells
Exit Sub


Case vbCancel
Cancel = True
Application.EnableEvents = True
Exit Sub

Case vbNo
ThisWorkbook.Close savechanges:=False

End Select

End Sub

*The only thing not working is Case vbNo (workbook not closing)