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

 

[redspanna]

Dufus

The code posted earlier....

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim myWs As String, ws As Workbook 
With Target 
   If .Address(0,0) <>"F27" Then Exit Sub 
   If .Value = "" Then Exit Sub 
   On Error Resume Next 
   Set ws = Sheets(.Text) 
   On Error GoTo 0 
   If Not ws Is Nothing Then 
      ws.Activate 
   Else 
      MsgBox .Value & " Sheet doesn't exist" 
   End If 
End  With 
End Sub

Kind of worked, however the response to every item selected from the menu was "xxxx Sheet doesn't exsist"
when clearly it did.

Now I'm realy confused ! - hope you can help

 

[Dufus]

The code I meant was:

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

Run it and copy paste the results.

 

[jindon]

Private Sub Worksheet_Change(ByVal Target As Range) 
With Target 
   If .Address(0,0) <>"F27" Then Exit Sub 
   If .Value = "" Then Exit Sub 
   On Error GoTo Last 
   Sheets(.Text).Activate
   Exit Sub
Last: 
      MsgBox .Value & " Sheet doesn't exist" 
End  With 
End Sub

 

[redspanna]

results as shown are :

test
Sheet2
SEP06
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

 

[redspanna]

Jindon


this code

Private Sub Worksheet_Change(ByVal Target As Range) 
With Target 
   If .Address(0,0) <>"F27" Then Exit Sub 
   If .Value = "" Then Exit Sub 
   On Error GoTo Last 
   Sheets(.Text).Activate 
   Exit Sub 
Last: 
      MsgBox .Value & " Sheet doesn't exist" 
End  With 
End Sub

did the trick..



YIPPPPPPEEEEEEE !!!!!!!!!!!!!!!!!


Thanks so much for all your patience with me on this