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

 

[timorrill]

Try this in your UserForm code:

Private Sub ListBox1_Click()
    If ListBox1.Value = "Apple" Then
        Sheet1.Activate
    ElseIf ListBox1.Value = "Orange" Then
        Sheet2.Activate
    End If
End Sub

 

[redspanna]

Hi,

I am not using a userform.
I have listed all my dropdown menu items in cells U4:U20 and then created a drop menu Data > Validation > List
so when the user selects an item from this list I want the relevant worksheet to open as mentioned above.

Sorry to be pain - but can you help anymore ?

Thanks

 

[jindon]

paste the code onto sheet module
assuming Cell A1 has validation list

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myWs As Integer
With Target
    If .Address(0,0) <> "A1" Then Exit Sub  '<- change A1, if needed
    If .Value = "" Then Exit Sub
    Select Case .Value
         Case "Apple" : myWs = 1
         Case "Orange" : myWs = 2
         Case Else : Exit Sub
    End Select
    Sheets(myWs).Activate
End With
End Sub

 

[redspanna]

Hi,

Can't get this to work...........

Cells U1:U20 is where the validation list items are hidden
Cell F27 is where the validation dropdown list is shown where the user selects an item from.
should the code....

If .Address(0,0) <> "A1" Then Exit Sub '<- change A1, if needed

be changed to

If .Address(0,0) <> "F27" Then Exit Sub '<- change A1, if needed
as this is where the menu is displayed or should it be "U1" as this is where the date validation list is hidden. I have tried both and I can't get it to work.
Sorry - but can you offer any more help again ?

 

[jindon]

OK
Alteration seems OK

Can you give us the actual items in the list?

 

[Doug.T]

Are your sheet names sheet1, sheet2?
If not then change:
myWs = 1 to myWs='mysheetname"
and myWs = 2 to myWs="Myothersheetname"

 

[redspanna]

Sure,

The items in the complete list are as follows

January 2005 stats log
February 2005 stats log
March 2005 stats log
April 2005 stats log
May 2005 stats log
June 2005 stats log
July 2005 stats log
August 2005 stats log
September 2005 stats log
October 2005 stats log
November 2005 stats log
December 2005 stats log
HCS proforma
Contacts
Non operational comms
post 2300hrs log


As I said this 'list' is held in cells U1:U20 and the actual drop down list (box) wihich the user choses from is cell F27

Hope this helps

Thanks

 

[jindon]

If your list items represent sheet names in your workbook then

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

 

[redspanna]

thanks for all ur help, I'll give this a try