Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach to a Menu of Multiple Print Ranges
Hi Bruce,
I'm using a variation of a menu I found online at j- walk.com called menumaker.xls at the link listed below: http://j-walk.com/ss/excel/tips/tip53.htm It is sufficient for most things but it is limited to three levels of menu planning. I think most users would find more than three levels of a menu structure far too hard to navigate - in that they typically won't remember where a particular menu item is in the hierarchy. What I am thinking about and I have no real idea of how to get started is to use a Level 2 on this Menu (If you're familiar with the menu tree he uses). The level 2 menu item will be "Print Options". On Level 3 would be various subcategories of printing I want to do. Based on the subcategory a user chooses, it would branch perhaps to a form, and the form would have some pull down items. Based on the selection in the pull down option, that should start a macro that prints the selected pre-defined range. Should I just go a totally different route rather than branch off from this custom menu tree? Or does this sound like a reasonable approach? Yes, that sounds reasonable, depending on the pull-down items that would appear on the form. If they'll be different for each sub-category then your approach sounds fine. If they'll be the same for each subcategory, you might want to consider putting the sub-category as a choice on the (one) form, so "Print Options" would bring up the dialog. Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach to a Menu of Multiple Print Ranges
-----Original Message-----
Yes, that sounds reasonable, depending on the pull-down items that would appear on the form. If they'll be different for each sub-category then your approach sounds fine. If they'll be the same for each subcategory, you might want to consider putting the sub-category as a choice on the (one) form, so "Print Options" would bring up the dialog. __________________________________________________ ________ Ok, I think I like this idea of a dialog box you mentioned here. Are you saying that I could have a 2nd level menu that would bring a dialog box that I could put some selections on it? Right now, I have a level 2 menu, which has a title "Go to/Print". Level 3 has 6 different items on it that are presently under construction. Think of the level 3 items as subcategories in which several print jobs can be derived from within each subcategory. What happens if you click one of those level 3 menu items, is it goes to a page. Here on this page, there may be three or four possible reports right under each other on this sheet. Right now, I have set up a button to allow the user to click to print that particular report, or they can click a 2nd button beside that to print two or more reports as the case may be that are on the particular page. Each print has a macro named print_xyz1, print_xyz2 etc. I know this sounds inefficient and otherwise just lacks <pzazz. I just need someone to get me started with some code that might allow for manageing all these reports that I print each month a little easier. In summary for the sake of discussion, there are six different subcategories with each subcategories having two or more separate print ranges to print each month. Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Approach to a Menu of Multiple Print Ranges
Stephen:
I guess I'm in over my head at this point. I don't have any experience with list boxes. I guess I need someone to point me to an online tutorial to where i can follow your instructions better on how to go about setting this stuff up. -----Original Message----- Hi Bruce, I just need someone to get me started with some code that might allow for manageing all these reports that I print each month a little easier. In summary for the sake of discussion, there are six different subcategories with each subcategories having two or more separate ranges to print each month. In that case, I would have a separate sheet somewhere (called shtLookup, say) that contains a table listing the category, subcategory, sheet and range to print for each report: A B 1 Category 1 2 Report 1.1 Sheet1!A1:G20 3 Report 1.2 Sheet1!A21:G30 4 Category 2 5 Report 2.1 Sheet2!A1:H30 Then just have a userform containing a 2-column (with ColumnWidth of ;0 to hide the second column) multi-select list box and an Print button. Your form would then have the code: Private Sub UserForm_Initialize() lbReports.List = ThisWorkbook.Worksheets("shtLookup") _ .Range("A1").CurrentRegion End Sub Private Sub btnPrint_Click() Dim i As Integer With lbReports For i = 0 To .ListCount If .Selected(i) And .List(i, 1) < "" Then Range(.List(i, 1)).PrintOut End If Next End With End Sub Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Menu | New Users to Excel | |||
Setting multiple ranges to print | Excel Discussion (Misc queries) | |||
Print Area ranges print on separate pages? | Excel Discussion (Misc queries) | |||
print menu | Excel Discussion (Misc queries) | |||
Multiple Print Ranges on one Worksheet | Excel Worksheet Functions |