create dropdown menu using Combo box

Hi,

I wish to place a dropdown menu on my worksheet that will alow the user to
select an item from it (items include subjects such as Food, Drink, Paper ,
Wood)
when one of these items have been selected a seperate sheet within my
workbook is opened containing specific date for that item
Can anybody offer any help
please please - I am a complete novice - so go easy uh !!!!

Thanks

Anthony

Hi,

Instead of using a Combo box it may be better to use Validation. These are
the setps you need to take to make it all work, you may want to print them,
you will have to change a few things for it to work for you, i.e. names of
sheets etc..

Assuming the drop down list is in Sheet 1

1) Have a list of all the sheets somewhere in Sheet1, in this example it
resides in range F1:F3.

2) Select cell A1, in the menu click Data the Validation, in the Allow box
select list.
In the source using that redish thing (range selector) select the range
F1:F3.
Or type in =F$1:$F$3. Then click OK.

You should now have a drop down list in Cell A1.

3) Press Ctrl and F11, this will take you to the Visiual Basic Editor.
4) On the left in the Explorer pane select the worksheet where we just
created the drop down list.
5) On the right, there is another blank pane with two drop down boxes,
select the General list and select Worksheet.
6) Paste the folowing code,

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

SheetName = Range("A1")

'Set the range for the for checking changes to A1:A1.
Set r = Intersect(Range("A1:A1"), Target)

'If the change in the worksheet is not in the tested range, exit the macro.
If r Is Nothing Then Exit Sub

' Select the sheet
Sheets(SheetName).Select



End Sub


7) Close the Visual Basic Editor by pressing Alt then Q.

Now when you select something from the list it will take you to that
worksheet.

Make sure the list of worksheets names is identical to the actual worksheet
names.

Any problems, post back

Hope that helps

____________________________
Naz
Lodon









"Anthony" wrote:

> Hi,
>
> I wish to place a dropdown menu on my worksheet that will alow the user to
> select an item from it (items include subjects such as Food, Drink, Paper ,
> Wood)
> when one of these items have been selected a seperate sheet within my
> workbook is opened containing specific date for that item
> Can anybody offer any help
> please please - I am a complete novice - so go easy uh !!!!
>
> Thanks
>
> Anthony