Excel2000:How to link Print Area with a named range

[Arvi Laanemets]

Hi


I'm designing an Excel application for printing shipping labels. The user
enters shipment data into a table (a row for every package), selects the
shipment, and on report page predesigned A5 format labels are filled with
data (texts, numbers, and barcodes)

As the number of packages in shipment varies, I have to find a way to avoid
empty labels to be printed. I have designed a dynamic named range, which
returns exactly the wanted range.

When on Sheet tab of Page Setup page I enter into Print area field the
formula '=MyRange', the Excel recognisez it and only filled labels are
printed. But when I open the Page Setup again, the named renge is replaced
with absolute range reference. And when I now select another shipment, the
wrong number of labels is printed.

Can someone help me with this? Thanks in advance

 

[JP]

You would probably need some kind of selection event
(Worksheet_SelectionChange) to reset the print area to your currently
selected range.

I know you might not want to hear this, but why not use MS Word to
print labels? It's already designed to handle your needs. You can even
set up a macro so that someone can enter the label information in a
spreadsheet, save and close, then open the Word doc and press a button
to print the label.


HTH,
JP

 

[ryguy7272]

If your named range is 'NamedRange' use this:
ActiveSheet.PageSetup.PrintArea = "NamedRange"


Regards,
Ryan---

 

[Pierre]

Hi

I'm designing an Excel application for printing shipping labels. The user
enters shipment data into a table (a row for every package), selects the
shipment, and on report page predesigned A5 format labels are filled with
data (texts, numbers, and barcodes)

As the number of packages in shipment varies, I have to find a way to avoid
empty labels to be printed. I have designed a dynamic named range, which
returns exactly the wanted range.

When on Sheet tab of Page Setup page I enter into Print area field the
formula '=MyRange', the Excel recognisez it and only filled labels are
printed. But when I open the Page Setup again, the named renge is replaced
with absolute range reference. And when I now select another shipment, the
wrong number of labels is printed.

Can someone help me with this? Thanks in advance

This worked for me, same scenario.
Pierre
http://groups.google.com/group/micr...cessary+(in+all+the+places).#2564cd3178abd614

 

[Arvi Laanemets]

Thanks Pierre, your solution was exactly what I was searching for!


Arvi Laanemets

Hi

I'm designing an Excel application for printing shipping labels. The user
enters shipment data into a table (a row for every package), selects the
shipment, and on report page predesigned A5 format labels are filled with
data (texts, numbers, and barcodes)

As the number of packages in shipment varies, I have to find a way to
avoid
empty labels to be printed. I have designed a dynamic named range, which
returns exactly the wanted range.

When on Sheet tab of Page Setup page I enter into Print area field the
formula '=MyRange', the Excel recognisez it and only filled labels are
printed. But when I open the Page Setup again, the named renge is replaced
with absolute range reference. And when I now select another shipment, the
wrong number of labels is printed.

Can someone help me with this? Thanks in advance

This worked for me, same scenario.
Pierre
http://groups.google.com/group/micr...cessary+(in+all+the+places).#2564cd3178abd614

 

[Arvi Laanemets]

Hi


In Excel it will be more user-friendly. Enter the data, and print
immediately, or at any time later. And the user gets a shipments database as
bonus too.

In my Excel application the barcode (code128) is generated by an UDF. And
for the rest no VBA code is needed at all. I had to use an UDF to generate
barcode, because the application has to work in computers with any system
code table - I think when all users (and myself) would use US Windows only,
then I could design it using worksheet functions only. But now I had to use
CharW() function from VBA to get some proper special characters - some ASCII
codes given in Code128 specification are returnig retangles (non-existing
character) otherwise for me.


Arvi Laanemets


You would probably need some kind of selection event
(Worksheet_SelectionChange) to reset the print area to your currently
selected range.

I know you might not want to hear this, but why not use MS Word to
print labels? It's already designed to handle your needs. You can even
set up a macro so that someone can enter the label information in a
spreadsheet, save and close, then open the Word doc and press a button
to print the label.


HTH,
JP