I'd create a separate addin workbook that contained a couple of macros. One
that would save and close the file and one that would open a new template file.
And this would keep the code separate from the data workbook, too.
If you want to try, create a new workbook and add this code to a General module:
Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()
Dim iCtr As Long
Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant
Call RemoveMenubar
MacNames = Array("OpenTemplate", _
"SaveAndClose")
CapNames = Array("Open New Template File", _
"Save and Close Active Workbook")
TipText = Array("Ready to start a new workbook?", _
"Finished with this one?")
With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating
For iCtr = LBound(MacNames) To UBound(MacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNames(iCtr)
.Style = msoButtonCaption
.TooltipText = TipText(iCtr)
End With
Next iCtr
End With
End Sub
Sub SaveAndClose()
Dim myPath As String
Dim myFileName As String
Dim OkToTryToSave As Boolean
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
OkToTryToSave = True
With ActiveWorkbook.Worksheets(1)
If .Range("A1").Value = "" Then
OkToTryToSave = False
End If
If IsDate(.Range("a2").Value) = False Then
OkToTryToSave = False
End If
If OkToTryToSave = False Then
MsgBox "Please check the account/date fields and try again"
Else
myFileName = .Range("a1").Value _
& "_" _
& Format(.Range("a2").Value, "yyyy-mm-dd") _
& ".xls"
On Error Resume Next
.Parent.SaveAs Filename:=myPath & myFileName, _
FileFormat:=xlWorkbookNormal
If Err.Number <> 0 Then
Err.Clear
MsgBox "File not saved and not closed!"
Else
.Parent.Close savechanges:=False
MsgBox "Saved to:" & vbLf & myPath & vbLf & myFileName
End If
End If
End With
End Sub
Sub OpenTemplate()
Dim TemplateName As String
Dim TestStr As String
Dim wkbk As Workbook
TemplateName = "C:\my documents\excel\book1.xls"
TestStr = ""
On Error Resume Next
TestStr = Dir(TemplateName)
On Error GoTo 0
If TestStr = "" Then
MsgBox "Design error--template not found"
Else
Set wkbk = Workbooks.Add(template:=TemplateName)
End If
End Sub
You'll have to change the paths, file names, range locations, worksheets to make
it work for your stuff.
Then save this workbook as an addin (at the bottom of the File|SaveAs|Save As
Type dropdown.
Then close excel and reopen excel.
Then open the addin whenever you want this functionality.
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
