menu select worksheet

[Guest]

I have over 100 worksheets. I need a simple and easy menu for the user when
they select the style# and color it takes them to that worksheet for that
style # and color. Style#'s are 770. 790, 795, 775 etc. and colors Red &
White, Navy & White, Forest & White etc. The Worksheets are label as 770rw,
770nw, 77ofw, 790rw, 790nw. 790fw, so forth.

Is this possible to do or not? If so can you give example program to build
on. Am I going in the worng direction, I am open any suggestion. Thanks

 

[Bob Phillips]

Where and how are styles and colours held?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I would start with 2 Lookup Tables probably out of sight :

Style
770
790
795
775

Color Code
Red & White rw
Navy & White nw
Forest & White fw

I would give the first range the name: rStyle
and the 2nd table I would name rColorTable and each col I would name rColor
and rCode

Then I would use one of the following methods for user selection:

A. Verification: In Cell A1 Use Data>Verification>List ---RefersTo:
=rStyle
In Cell A2 Use Data>Verification>List
....RefersTo: =rColor
In Cell A3 enter
=VLookup(A2,rColorTable,rCode,false)
In Cell A4 enter =A1&A3
'this will get the Sheetname into A4
Then you could use a Cmd Button to navigate to that sheet:
Sub GoToMySheet()
dim MySh as String
MySh = Range("A4")
Sheets(MySh).Activate
End Sub

B. You could also use comboboxes for user selection... a bit more complex

You could also use a worksheet_Change event (as opposed to the command
button to make the navigation.

does this hlp get you started?

 

[Tom Ogilvy]

http://tinyurl.com/3ncod
What was wrong with the answer back on Mar 4/5

From: Compnerd ([email protected])
Subject: Re: Having A Macro Run When A Selection Is Made In A List Box


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-03-05 09:58:00 PST

I was not clear on What I want. I am trying to setup a Menu page that has
20
drop-down list that the user can select by the style number and color. When
they make the choice, it will take them to a worksheet. One of the drop-down
list would say 770 with the color choices of Red-White, Forest-White, or
Navy-White.
I would like to use Control Toolbox Combination Box-change the Style
property to 2 and write my Macro like these

Sub redwhite770()
'
' redwhite770 Macro
'
'
Sheets("770rw").Select
End Sub

I need the code for the combo box that when user select the color it will
run the Macro.

 

[Guest]

I Missing something, In A3 I am getting #Ref , Here What I have done:
Sheet1 List the Styles from A1-A5, Sheet3 List the Color and Code in 2
columns.
Insert>Name>Define- Type in rStyle refer to =Sheet1!$A$1:$A$5
Insert>Name>Define -Type in rColor refer to =Sheet2!$A$1:$A$4
Insert>Name>Define- Type in rCode refer to =Sheet2!$B$1:$B$4
Insert>Name>Define- Type in rColorTable refer to Sheet2!$B$2:$B$4
and follow instruction place on sheet3 for A1 A2 A3 A4. A1 and A2 Works fine.

 

[Tom Ogilvy]

=VLookup(A2,rColorTable,rCode,false)
should be

=VLookup(A2,rColorTable,2,false)

rCode isn't used.

--
Regards,
Tom Ogilvy

I Missing something, In A3 I am getting #Ref , Here What I have done:
Sheet1 List the Styles from A1-A5, Sheet3 List the Color and Code in 2
columns.
Insert>Name>Define- Type in rStyle refer to =Sheet1!$A$1:$A$5
Insert>Name>Define -Type in rColor refer to =Sheet2!$A$1:$A$4
Insert>Name>Define- Type in rCode refer to =Sheet2!$B$1:$B$4
Insert>Name>Define- Type in rColorTable refer to Sheet2!$B$2:$B$4
and follow instruction place on sheet3 for A1 A2 A3 A4. A1 and A2 Works fine.

Thanks

 

[Guest]

I am lost how this works. On march 4, 5 my thinking was to create 20 or more
drop-down list on the same page. I found out that it was to messy and
cluttered. I like method that gocush presents. Does this macro do the same
thing in combo style format? If does I need little more detailed
instructions. thanks Also thanks for clarifing gocush validation code.

 

[Tom Ogilvy]

Sounds like your set. Would be a waste of our time to pursue this further.

 

[Guest]

I love the progam that you created with Dynamic Dependent Dropdowns on the
web site
http://www.xldynamic.com/source/xld.Dropdowns.html. I want to use this as
my Menu routine with combobox
In addition I need function that when user make the second selection it
takes them to that worksheet for that style and color or use a command button

On the Data worksheet, I would change List1 to Style # and Change theList2
to Colors. For example in Column B Row1 770 , row 2-25 Red & White, Navy &
White, Forest & White etc.

Thanks

 

[Bob Phillips]

Are you asking how to do it?

Assuming that you have the 2 combos working in unison, just add a click
event to the second and add activate code in there.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes these is 2 combo working unison. YesI need help with the code and
palcement.