Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Excel2000:How to link Print Area with a named range

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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


  #2   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default Excel2000:How to link Print Area with a named range

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

On Apr 8, 8:31*am, "Arvi Laanemets" wrote:
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


  #3   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default Excel2000:How to link Print Area with a named range

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


Regards,
Ryan---
--
RyGuy


"Arvi Laanemets" wrote:

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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



  #4   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default Excel2000:How to link Print Area with a named range

On Apr 8, 7:31*am, "Arvi Laanemets" wrote:
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


This worked for me, same scenario.
Pierre
http://groups.google.com/group/micro...c d3178abd614

  #5   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Excel2000:How to link Print Area with a named range

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


Arvi Laanemets


"Pierre" wrote in message
...
On Apr 8, 7:31 am, "Arvi Laanemets" wrote:
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


This worked for me, same scenario.
Pierre
http://groups.google.com/group/micro...c d3178abd614




  #6   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Excel2000:How to link Print Area with a named range

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


"JP" wrote in message
...
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

On Apr 8, 8:31 am, "Arvi Laanemets" wrote:
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link to a named range in another worksheet that is sorted frequent ShortBlonde Excel Worksheet Functions 2 March 18th 08 04:32 PM
External link to Named Range Fleone Excel Discussion (Misc queries) 0 December 18th 07 09:52 PM
Create a link to print a certain range AdamCPTD Excel Discussion (Misc queries) 1 July 17th 06 09:57 PM
Print area/range Mike D. Excel Discussion (Misc queries) 2 June 30th 05 07:07 PM
Excel2000: Custom data validation and named ranges Arvi Laanemets Excel Discussion (Misc queries) 9 December 10th 04 08:05 PM


All times are GMT +1. The time now is 02:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"