I take it the project file is either a template file ("xlt") or a file setup
on the main file server with it set to read only (if it's an "xls" file)?
If so, you can setup a second file that only you can modify. The file the
other people open up and then save as a new file name, it can be referenced
to the other central file that contains your dates.
Example:
People have the "Organization" folder on the file server reference as their
"O:" drive.
Project template file is saved as
"O:\Administration\Project2007Template.xls" with it's "ReadOnly" attribute
set to "True"
Date file is saved as "O:\Adminstration\DatesPrintAllowed.xls", also setup
as "ReadOnly", though you would remove it to allow yourself to be able to
change the dates yourself.
If you feel more comfortable, and provided you have the necessary
priviledges to do so, you can use Window's Security system instead for
setting file access. However, file level security doesn't work too good for
Excel files cause when Excel saves a workbook, the file level stuff is
completely lost due to Excel deleting the old workbook, then saving the
workbook as a new workbook using the windows security settings on the folder
level to become the new file level security settings.
The workbook would have the first column setup as the "Start Date" and the
second column setup as the "End Date" as for when the project workbooks
could be printed. Just to keep it simple, let's say the name of the
worksheet it's on is by the name of "Print Dates" and the dates starts on
row 2 with the row header being on row 1. Note: List will need to be in
ascending order for this to work.
Now for the VBA side of it, you would have it setup as the following:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Declare variables
Dim wbkDates As Excel.Workbook, wbk As Excel.Workbook, lngDateRow As
Long, wshPrintDates as Excel.Worksheet
'Initialize variables
For each wbk in Workbooks
If wbk.Name = "DatesPrintAllowed.xls" Then
Set wbkDates = wbk
Exit For
End If
Next
Set wbk = Nothing
If wbkDates is Nothing Then
Set wbkDates =
Workbooks.Open("O:\Administration\DatesPrintAllowed.xls",0,True)
End If
set wshPrintDates = wbkDates.Worksheets("PrintDates")
lngDateRow =
Application.WorksheetFunction.MATCH(Date,wshPrintDates.Range("A:A").Address(False,
False, xlA1, True)
'Perform date check
If lngDateRow > 0 Then
'We already know the start date is less than or equal to the current
date for the row the match function returned.
'If the End Date is less than the current date, then the current
date isn't within the print date range.
'Of course, this assumes the dates are reported into the file
appropriately.
If VBA.Int(wshPrintDates.Cells(lngDateRow,2).Value2) < CLng(Date)
Then
Cancel = True
End If
Else
Cancel = True
End If
'Clean up process
Set wshPrintDates = Nothing
wbkDates.Saved = True
wbkDates.Close
Set wbkDates = Nothing
End Sub
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000