Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are extremely
large, (2,000 pages). The macro is being executed but the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub

By the time your code tries to execute, the print has long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the Print attempt? That's
the appropriate place to do so. Insert the line Cancel = True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are extremely
large, (2,000 pages). The macro is being executed but the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub

If you don't want to use VBA
(user can open the file without macro's also)

You can also set the print range on each sheet to a1:a3 or so.
Then when they hit the print button only this cells are printed.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Randall Arnold" wrote in message ...
By the time your code tries to execute, the print has long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the Print attempt? That's
the appropriate place to do so. Insert the line Cancel = True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are extremely
large, (2,000 pages). The macro is being executed but the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub

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

Place this event in the Thisworkbook module and you can't print
as long the user is enabled macro's when opening the file

Right click on the Excel icon next to File in the menubar
Choose view code
paste this event there
Alt-Q to go back to Excel
Save the file


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Phil" wrote in message ...
Randall;
I tried something like this.
According to the VB help menu, it is supposed to abort the
print job but it is not.
When I run it the worksheet still prints.
Also, If you are referring to the "Tools-Options" in
Excel, I don't see anything that would prevent printing in
there.

Sub NoPrint()
ActiveWindow.SelectedSheets
Workbook_BeforePrint
Cancel = True
End Sub

Thanks
Phil

-----Original Message-----
By the time your code tries to execute, the print has
long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the Print
attempt? That's
the appropriate place to do so. Insert the line Cancel =
True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets
from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are
extremely
large, (2,000 pages). The macro is being executed but
the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub




.

Thanks Ron.

What I was not doing was saving the workbook, closing it
and then trying to print after re-opening.

-----Original Message-----
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Place this event in the Thisworkbook module and you can't
print
as long the user is enabled macro's when opening the file

Right click on the Excel icon next to File in the menubar
Choose view code
paste this event there
Alt-Q to go back to Excel
Save the file


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Phil" wrote in message
...
Randall;
I tried something like this.
According to the VB help menu, it is supposed to abort
the
print job but it is not.
When I run it the worksheet still prints.
Also, If you are referring to the "Tools-Options" in
Excel, I don't see anything that would prevent printing
in
there.

Sub NoPrint()
ActiveWindow.SelectedSheets
Workbook_BeforePrint
Cancel = True
End Sub

Thanks
Phil

-----Original Message-----
By the time your code tries to execute, the print has
long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the
Print
attempt? That's
the appropriate place to do so. Insert the line
Cancel =
True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets
from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to
prevent
users from printing either sheet since they are
extremely
large, (2,000 pages). The macro is being executed but
the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large
to
Print")
ActiveWorkbook.Close
End If
End Sub




.



.

Beat me to it!

Of course, he'll want the IF/THEN logic as well, probably. But I'm betting
he can get it from here.

Randall

"Ron de Bruin" wrote in message
...
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Place this event in the Thisworkbook module and you can't print
as long the user is enabled macro's when opening the file

Right click on the Excel icon next to File in the menubar
Choose view code
paste this event there
Alt-Q to go back to Excel
Save the file


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Phil" wrote in message
...
Randall;
I tried something like this.
According to the VB help menu, it is supposed to abort the
print job but it is not.
When I run it the worksheet still prints.
Also, If you are referring to the "Tools-Options" in
Excel, I don't see anything that would prevent printing in
there.

Sub NoPrint()
ActiveWindow.SelectedSheets
Workbook_BeforePrint
Cancel = True
End Sub

Thanks
Phil

-----Original Message-----
By the time your code tries to execute, the print has
long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the Print
attempt? That's
the appropriate place to do so. Insert the line Cancel =
True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets
from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are
extremely
large, (2,000 pages). The macro is being executed but
the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub




.

Got it!

I have a nice little message that pops up to inform the
user the file is too large to print.
Thanks much
Phil

-----Original Message-----
Beat me to it!

Of course, he'll want the IF/THEN logic as well,
probably. But I'm betting
he can get it from here.

Randall

"Ron de Bruin" wrote in message
...
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Place this event in the Thisworkbook module and you
can't print
as long the user is enabled macro's when opening the
file

Right click on the Excel icon next to File in the
menubar
Choose view code
paste this event there
Alt-Q to go back to Excel
Save the file


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Phil" wrote in message
...
Randall;
I tried something like this.
According to the VB help menu, it is supposed to
abort the
print job but it is not.
When I run it the worksheet still prints.
Also, If you are referring to the "Tools-Options" in
Excel, I don't see anything that would prevent
printing in
there.

Sub NoPrint()
ActiveWindow.SelectedSheets
Workbook_BeforePrint
Cancel = True
End Sub

Thanks
Phil

-----Original Message-----
By the time your code tries to execute, the print has
long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the
Print
attempt? That's
the appropriate place to do so. Insert the line
Cancel =
True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent
sheets
from being printed as
an option, but I may be wrong.

Randall Arnold

"Phil" wrote in message
...
Good morning; I am using Windows 2000 and Excel
2000.
I have the following macro for a read only file.
The
Workbook file contains two worksheets. I need to
prevent
users from printing either sheet since they are
extremely
large, (2,000 pages). The macro is being executed
but
the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too
Large to
Print")
ActiveWorkbook.Close
End If
End Sub




.





.