Beforeprint not executing

[Salgud]

I'm missing something simple. I have the following code in ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

End Sub

But it's not happening, just prints the selected cell instead of the
Current Region. An Edit Goto Current Region selects the correct Current
Region. I copied the code from an online tutorial, and read a reviewed the
VBA Help, and can't figure out what's wrong. I'm in Win2000, XL2003.
Anybody see what I'm missing?

TIA

 

[Jim Cone]

I tested your code by adding a couple of lines and then trying to print.
It won't actually print anything, it just tells you what the print area is.
Give it a try, maybe it will provide a clue...
'---
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address
MsgBox ActiveSheet.PageSetup.PrintArea
Cancel = True
End Sub
'---
And...
The Before_Print event only fires once and the code you have only
applies to the sheet you are looking at when you hit the print button.
If you are printing multiple sheets and want a particular cells current
region to print on each sheet, then you will need different code.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Salgud" <[email protected]>
wrote in message
I'm missing something simple. I have the following code in ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

End Sub

But it's not happening, just prints the selected cell instead of the
Current Region. An Edit Goto Current Region selects the correct Current
Region. I copied the code from an online tutorial, and read a reviewed the
VBA Help, and can't figure out what's wrong. I'm in Win2000, XL2003.
Anybody see what I'm missing?

TIA

 

[Don Guillett Excel MVP]

I tested your code by adding a couple of lines and then trying to print.
It won't actually print anything, it just tells you what the print area is.
Give it a try, maybe it will provide a clue...
'---
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address
MsgBox ActiveSheet.PageSetup.PrintArea
Cancel = True
End Sub
'---
And...
The Before_Print event only fires once  and the code you have only
applies to the sheet you are looking at when you hit the print button.
If you are printing multiple sheets and want a particular cells current
region to print on each sheet, then you will need different code.
--
Jim Cone
Portland, Oregon  USAhttp://www.mediafire.com/PrimitiveSoftware

.
.
.

"Salgud" <[email protected]>
wrote in messageI'm missing something simple. I have the following code in ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

End Sub

But it's not happening, just prints the selected cell instead of the
Current Region. An Edit Goto Current Region selects the correct Current
Region. I copied the code from an online tutorial, and read a reviewed the
VBA Help, and can't figure out what's wrong. I'm in Win2000, XL2003.
Anybody see what I'm missing?

TIA

From the vba help index xl2003

CurrentRegion Property
See AlsoApplies ToExampleSpecificsReturns a Range object that
represents the current region. The current region is a range bounded
by any combination of blank rows and blank columns. Read-only.

Remarks
This property is useful for many operations that automatically expand
the selection to include the entire current region, such as the
AutoFormat method.

This property cannot be used on a protected worksheet.

Example
This example selects the current region on Sheet1.

Worksheets("Sheet1").Activate
ActiveCell.CurrentRegion.Select

This example assumes that you have a table on Sheet1 that has a header
row. The example selects the table, without selecting the header row.
The active cell must be somewhere in the table before you run the
example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select

 

[Salgud]

Thanks to both of you for your replies. The problem was that I had XL
re-installed some time back, hadn't been doing any macros since, and had
macro security set to High (default), so no macros could run!

Thanks again.