2 part. I would like to auto save my worksheets when completed with the acct
name and date on the tab. also is it possible to have the master sheet
reappear blank after being saved. I hope that makes sense.

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 he
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.)

doss04 wrote:

2 part. I would like to auto save my worksheets when completed with the acct
name and date on the tab. also is it possible to have the master sheet
reappear blank after being saved. I hope that makes sense.

--

Dave Peterson

Thanks Dave:
I've run a couple of simple macros. This one is pretty big. I'll need to do
some homework on where i need to make the changes and how the add- in works.
I'll let you know how it goes. Thanks again

"Dave Peterson" wrote:

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 he
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.)

doss04 wrote:

2 part. I would like to auto save my worksheets when completed with the acct
name and date on the tab. also is it possible to have the master sheet
reappear blank after being saved. I hope that makes sense.

--

Dave Peterson