drop down lists

[redspanna]

Hi

I have a drop down list which contains aprox 15 items, I want the user to be able to select an item from that list and then a certain worksheet be opened relating to that list.

So if Apple is select from the list - sheet 1 is opened
if Orange is selcted from the list - sheet 2 is opened
...and so on

can anybody provide some code for a novice

Many thanks in advance

 

[venkat1926]

yet another solution
see sample sheet called "main"
the data validation cell is A1
the data validation list is in column B
fill column C with sheet names. if you type inC1 -sheet1 and copy down all the sheet names will be entered.
now use this macro

Sub gotosheet()
Worksheets("main").Activate
y = Range("a1").Value
With Range("B1:B15")
Set cfind = .Find(what:=y, lookat:=xlWhole)
End With
Worksheets(cfind.Offset(0, 1).Value).Activate
End Sub

 

[redspanna]

Hi all,

Sorry I am back still trying to get this to work !

I have tried the code above but after selecting **ANY** of the items from the drop down list I getthe respose "xxxx Sheet doesn't exist"

when in fact it does.

Any ideas ??

Thanks

 

[jindon]

Hi all,

Sorry I am back still trying to get this to work !

I have tried the code above but after selecting **ANY** of the items from the drop down list I getthe respose "xxxx Sheet doesn't exist"

when in fact it does.

Any ideas ??

Thanks

Can you give us the list of:

1) Actual sheet names
2) dropdown list

?

 

[redspanna]

Hi, sure I can - they are both exactly the same :-

Jan05
Feb05
Mar05
Apr05
May05
Jun05
Jul05
Aug05
Sep05
Oct05
Nov05
Dec05
Jan06
Feb06
Mar06
Apr06
May06
Jun06
Jul06
Aug06
HCS proforma
Contacts
non operational comms
post 2300hrs log


The above list is in cells U1:U24 and the drop down list F27

Hope this helps and pls ask any other questions
Look fwd to ur reply
regards

 

[jindon]

OK
Add this code to sheet module and run once,
it will list the sheet names in the range.
then try again.

Sub test()
Dim i As Integer
With Range("U1")
     For i = 1 To Sheets.Count
         .Offset(i-1).Value = Sheets(i).Name
     Next
End With
End Sub

 

[redspanna]

OK
Add this code to sheet module and run once,
it will list the sheet names in the range.
then try again.

Ok I have added the code and run it , however I am still getting the same response that the sheets don't exsist !!!!!!!!!!!

 

[Dufus]

redspanna,

You gave this list:
January 2005 stats log
February 2005 stats log
March 2005 stats log
April 2005 stats log

Is that a list of the sheet names or what's in the drop down list?

You also gave this list:
Jan05
Feb05
Mar05
Apr05

What is this list?

Dufus

 

[redspanna]

Hi Dufus,

The validation list and the actual names of the worksheets are exactly the same, ie :

HCS proforma
Contacts
non operational comms
nightshift brief
post 2300hrs log
Jan05
Feb05
Mar05
Apr05
May05
Jun05
Jul05
Aug05
Sep05
Oct05
Nov05
Dec05
Jan06
Feb06
Mar06
Apr06
May06
Jun06
Jul06
Aug06
Sep06


hope this helps

 

[jindon]

If the items in the dropdown list are exactly the same as sheet names,
code should worki

 

[Dufus]

When you run jindon's macro, what error do you get (use the exact wording) and which line of the code causes the error?