VBA PrintArea setting problem.

[Justin C]

Excel 2004

I'm trying to set the print area of a spreadsheet with VBA. I can get it
to make the selection, and what I've found for setting the print area
(not being to VBA aware) is: ActiveSheet.PageSetup.PrintArea = Selection
However, though this selects the range I want, the print area is still
the entire spreadsheet.

If I use Selection.PrintPreview I get just the selection, and can print
the selection I want from here, but I don't get access to the print
dialogue box, which I need to set certain printer options.

Any suggestions?

Is this question more suited to a different Excel group? If so, and you
can suggest one, I'd be grateful for the pointer.

Thank you for any help you can give.

Justin

 

[Bob Greenblatt]

Excel 2004

I'm trying to set the print area of a spreadsheet with VBA. I can get it
to make the selection, and what I've found for setting the print area
(not being to VBA aware) is: ActiveSheet.PageSetup.PrintArea = Selection
However, though this selects the range I want, the print area is still
the entire spreadsheet.

If I use Selection.PrintPreview I get just the selection, and can print
the selection I want from here, but I don't get access to the print
dialogue box, which I need to set certain printer options.

Any suggestions?

Is this question more suited to a different Excel group? If so, and you
can suggest one, I'd be grateful for the pointer.

Thank you for any help you can give.

Justin

Nope, this is the right group. The best bet is t define a name and have it
refer to the range you want. So, it should look something like:
Workbooks("whateverthenameis").names.add name:="Print_Area",
refersto:=selection
Or:

refersto:=sheets("nameofsheet").range(cells(1,1),cells(lastrow,lastcolumn))

 

[Justin C]

Nope, this is the right group. The best bet is t define a name and have it
refer to the range you want. So, it should look something like:
Workbooks("whateverthenameis").names.add name:="Print_Area",
refersto:=selection
Or:

refersto:=sheets("nameofsheet").range(cells(1,1),cells(lastrow,lastcolumn))

Just to clarify: name the range, and then pass the name of the range to
the other command like this: ActiveSheet.PageSetup.PrintArea =
"Print_Area".

I'll give it a go thanks for your help.

Justin.

 

[Justin C]

Workbooks("whateverthenameis").names.add name:="Print_Area",
refersto:=selection

Excellent, thank you for this. It works exactly how I want.

It does, however, seem strange to me that I can define an name as the
current selection, and then set the print area to that name, but not
miss out what appears to be a redundant step.

Or:

refersto:=sheets("nameofsheet").range(cells(1,1),cells(lastrow,lastcolumn))

The first one worked for me, I can see what's going on here, but for
clarity (understandability - 'cos I'm not any good with VBA) I've used
the first.

Justin.

 

[Bob Greenblatt]

Excellent, thank you for this. It works exactly how I want.

It does, however, seem strange to me that I can define an name as the
current selection, and then set the print area to that name, but not
miss out what appears to be a redundant step.

I'm not sure I understand what you are saying. The sheet's print area is the
reference assigned to the name "Print_Area" the code above is a single step,
and not redundant.

 

[Justin C]

I'm not sure I understand what you are saying. The sheet's print area is the
reference assigned to the name "Print_Area" the code above is a single step,
and not redundant.

I could be being a bit dense here. Are you saying that the part
name:="Print_Area" is reserved name in Excel, and is not an arbitrary
reference to the selection? It couldn't be, for example, be
name:="George", and then need: ActiveSheet.PageSetup.PrintArea = "George"?

Apologies if I'm being a bit slow understanding, I'm very new to VBA,
most of my coding is Perl - and I'm not great at that either!

Justin.

 

[Bob Greenblatt]

Justin,

I could be being a bit dense here. Are you saying that the part
name:="Print_Area" is reserved name in Excel, and is not an arbitrary
reference to the selection?

That is exactly right.

It couldn't be, for example, be
name:="George", and then need: ActiveSheet.PageSetup.PrintArea = "George"?

But, it indeed not only could be, it is! Try it! Assuming the Name George is
a valid reference, running the above code ADDS the name Print_Area to the
sheet referencing the range referred to by George.

Apologies if I'm being a bit slow understanding, I'm very new to VBA,
most of my coding is Perl - and I'm not great at that either!

Justin.

No apologies necessary. This how how one learns this stuff. It's how I and
most likely a whole bunch of others here did also.

 

[Justin C]

Justin,
That is exactly right.

But, it indeed not only could be, it is! Try it! Assuming the Name George is
a valid reference, running the above code ADDS the name Print_Area to the
sheet referencing the range referred to by George.

No apologies necessary. This how how one learns this stuff. It's how I and
most likely a whole bunch of others here did also.

It's all good! Thanks for the help. I've been a bit busy with 'proper'
work, I should be able to get back to this tomorrow.

Justin.