Hi,

I share a workbook with several sheets and macros with my officemates. I
have this macro so they cannot print any of the worksheets.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

However, when I out of town, and the reports needs to be printed, I'd like
to e-mail them another workbook to edit the macro above and replace it with
the macro below.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date < VBA.DateSerial(2007, 8, 17) Then
Cancel = True
End If
End Sub

The original workbook VBA Project Password is "OPENPW". Both workbooks will
have the same VBA Project Password.

Thank you.

Why not setup a central workbook with explicit dates in it as the dates when
the workbook can print? Once you have this central workbook setup as such,
you will then have the individual workbooks refer to the central one to
check if it can be printed that day or not. This will avoid a lot of the
issues of having to deal with VBE coding and code trust issues.

Of course, this is assuming everyone using and may need to print the
workbook would have access to the central file.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Danny" wrote in message
...
Hi,

I share a workbook with several sheets and macros with my officemates. I
have this macro so they cannot print any of the worksheets.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

However, when I out of town, and the reports needs to be printed, I'd like
to e-mail them another workbook to edit the macro above and replace it
with
the macro below.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date < VBA.DateSerial(2007, 8, 17) Then
Cancel = True
End If
End Sub

The original workbook VBA Project Password is "OPENPW". Both workbooks
will
have the same VBA Project Password.

Thank you.

Hi Ronald,

My mistake, I should have been very clear when I said "share" with my
officemates. They use the workbook for their own project and save it under a
new name. They complete each project from 2 to 5 days.

When they complete the project that's the time they forward it to me to
print their worksheets.

Thank you.

"Ronald Dodge" wrote:

Why not setup a central workbook with explicit dates in it as the dates when
the workbook can print? Once you have this central workbook setup as such,
you will then have the individual workbooks refer to the central one to
check if it can be printed that day or not. This will avoid a lot of the
issues of having to deal with VBE coding and code trust issues.

Of course, this is assuming everyone using and may need to print the
workbook would have access to the central file.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Danny" wrote in message
...
Hi,

I share a workbook with several sheets and macros with my officemates. I
have this macro so they cannot print any of the worksheets.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

However, when I out of town, and the reports needs to be printed, I'd like
to e-mail them another workbook to edit the macro above and replace it
with
the macro below.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date < VBA.DateSerial(2007, 8, 17) Then
Cancel = True
End If
End Sub

The original workbook VBA Project Password is "OPENPW". Both workbooks
will
have the same VBA Project Password.

Thank you.

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\DatesPrintAllowe d.xls",0,True)
End If
set wshPrintDates = wbkDates.Worksheets("PrintDates")
lngDateRow =
Application.WorksheetFunction.MATCH(Date,wshPrintD ates.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

"Danny" wrote in message
...
Hi Ronald,

My mistake, I should have been very clear when I said "share" with my
officemates. They use the workbook for their own project and save it under
a
new name. They complete each project from 2 to 5 days.

When they complete the project that's the time they forward it to me to
print their worksheets.

Thank you.

"Ronald Dodge" wrote:

Why not setup a central workbook with explicit dates in it as the dates
when
the workbook can print? Once you have this central workbook setup as
such,
you will then have the individual workbooks refer to the central one to
check if it can be printed that day or not. This will avoid a lot of the
issues of having to deal with VBE coding and code trust issues.

Of course, this is assuming everyone using and may need to print the
workbook would have access to the central file.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Danny" wrote in message
...
Hi,

I share a workbook with several sheets and macros with my officemates.
I
have this macro so they cannot print any of the worksheets.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

However, when I out of town, and the reports needs to be printed, I'd
like
to e-mail them another workbook to edit the macro above and replace it
with
the macro below.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date < VBA.DateSerial(2007, 8, 17) Then
Cancel = True
End If
End Sub

The original workbook VBA Project Password is "OPENPW". Both workbooks
will
have the same VBA Project Password.

Thank you.

Thank you so much for your time and effort Ronald.
It will take some time before I can understand everything below.
The template I have is an "xls" file.
All the macros I came up with to make the "template" came from helpful
individuals like yourself from this NG.
I posted another question (Password Change with Macro - based on date)
this morning hoping to get an answer.

"Ronald Dodge" wrote:

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\DatesPrintAllowe d.xls",0,True)
End If
set wshPrintDates = wbkDates.Worksheets("PrintDates")
lngDateRow =
Application.WorksheetFunction.MATCH(Date,wshPrintD ates.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

"Danny" wrote in message
...
Hi Ronald,

My mistake, I should have been very clear when I said "share" with my
officemates. They use the workbook for their own project and save it under
a
new name. They complete each project from 2 to 5 days.

When they complete the project that's the time they forward it to me to
print their worksheets.

Thank you.

"Ronald Dodge" wrote:

Why not setup a central workbook with explicit dates in it as the dates
when
the workbook can print? Once you have this central workbook setup as
such,
you will then have the individual workbooks refer to the central one to
check if it can be printed that day or not. This will avoid a lot of the
issues of having to deal with VBE coding and code trust issues.

Of course, this is assuming everyone using and may need to print the
workbook would have access to the central file.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Danny" wrote in message
...
Hi,

I share a workbook with several sheets and macros with my officemates.
I
have this macro so they cannot print any of the worksheets.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

However, when I out of town, and the reports needs to be printed, I'd
like
to e-mail them another workbook to edit the macro above and replace it
with
the macro below.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date < VBA.DateSerial(2007, 8, 17) Then
Cancel = True
End If
End Sub

The original workbook VBA Project Password is "OPENPW". Both workbooks
will
have the same VBA Project Password.

Thank you.