drop down lists

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Try this in your UserForm code:

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

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
paste the code onto sheet module
assuming Cell A1 has validation list
Code:
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OK
Alteration seems OK

Can you give us the actual items in the list?
 

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80

ADVERTISEMENT

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

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
If your list items represent sheet names in your workbook then
Code:
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
thanks for all ur help, I'll give this a try
 

Forum statistics

Threads
1,141,300
Messages
5,705,577
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top