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

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

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
print
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

.