Not meaning to ad to your already "plate full", but I thought I'd share
a concept here that fits the topic.
The project is a single-file daily scheduler that uses a sheet template
for each day of a month. The task of the project is to 'schedule'
fitness instructors at a gym for assignments during their shift.
There are 12 workbooks named for a fiscal period of 1 calendar year.
The template workbook has 31 copies of the daily scheduler template
sheet.
Each day when the file is opened this happens...
Sub Auto_Open()
Dim wksToday As Worksheet
InitGlobals '//initialize global variables
'Setup ThisWorkbook.Name to match GymID
If Not InStr(ThisWorkbbok.Name, gsThisGym) Then
ThisWorkbook.SaveAs gsAppPath & gsThisGym _
& Format(Month(Date()), "Mmm")
& ThisWorkbook.Name
End If
Set wksToday = Sheets(Day(Date())
With wksToday: .Visible = True: .Activate: End With
InitDailyWks '//setup default sheet info
End Sub
...which only activates the appropriate sheet on/for days the gym is
open.
The InitDailyWks routine inserts the date field on the template with a
long date format ("day Month dd, yyyy"), and fills in the gym location
details.
The InitGlobals routine loads values into global scope variables used
by a project. This and the variable declarations reside in the same
module as the AutoMacros, replacing the Workbook_Open event...
Sub InitGlobals()
gsAppPath = ThisWorkbook.Path & "\"
If Not bNameExists("GymID") Then Setup_ThisGym
gsThisGym = "GymID" _
& Mid(ThisWorkbook.Names("GymID").RefersTo, 2) & "_"
End Sub
Function bNameExists(sName$, Optional Wks As Worksheet) As Boolean
' Checks if sName exists in Wks
' Arguments:
' sName The defined name to check for
' Wks A ref to the Wkb or Wks being checked
' Returns:
' True if name exists
Dim x As Object
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Set x = Wks.Names(sName): bNameExists = (Err = 0)
End Function
...where InitGlobals also validates globals if their values aren't set.
The Setup_ThisGym routine creates local scope defined names for each
template sheet for "GymID" and "GymInfo". (This info is provided by the
user at 1st startup only) The names are local scope so the head office
can merge daily sheets for each month in a consolidation file for the
year, without raising name conflicts. Also, each gym has its own sheet
in the merged file, and each month gets a row in an outlined area for
that month.
Fortunately, this client only has 2 locations and so managing things
this way works for them. I propose a different approach if they add
more locations whereby the project converts to an addin that uses a
single daily scheduker worksheet template and starts new workbooks
based on month name for any calendar year. This would greatly simplify
ongoing project management/maintenance. Also, the distributed addin
won't require any head office code, making that a separate addin that
can easily be made a 'plugin' to the main addin installed at head
office if it needs its own scheduler. This, of course, changes the
project structure so it's no longer a single-file solution!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
