Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2000:How to link Print Area with a named range | Excel Discussion (Misc queries) | |||
Link to a named range in another worksheet that is sorted frequent | Excel Worksheet Functions | |||
External link to Named Range | Excel Discussion (Misc queries) | |||
Print area/range | Excel Discussion (Misc queries) | |||
Excel2000: Custom data validation and named ranges | Excel Discussion (Misc queries) |