Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks

Hi Pat
assuming that your validation listbox is in cell A1 put the following
code in the worksheet module of this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank


Pat wrote:
Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks

I've tried this and it doesn't completely work. When I
select the name it doesn't go to the sheet. If however, I
activate A1 (by entering the cell then pressing enter) it
works. Any way to change to code to do that?

-----Original Message-----
Hi Pat
assuming that your validation listbox is in cell A1 put
the following
code in the worksheet module of this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank


Pat wrote:
Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks


.

Pat,

I bet you are using Excel 97? Excel 97 Data Validation doesn't trigger the
Change event.

If this is the case, put a link to A1 in another cell, =A1 (you can make
the font white to hide it). Then put Frank's code into the
Worksheet_Calculate event.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Pat" wrote in message
...
I've tried this and it doesn't completely work. When I
select the name it doesn't go to the sheet. If however, I
activate A1 (by entering the cell then pressing enter) it
works. Any way to change to code to do that?

-----Original Message-----
Hi Pat
assuming that your validation listbox is in cell A1 put
the following
code in the worksheet module of this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank


Pat wrote:
Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks


.

Hi Pat
that works fine for me. A1 stores my data validation listbox. I select
an entry from this list and are taken to this sheet. What kind of data
validation have you used?

Frank

Pat wrote:
I've tried this and it doesn't completely work. When I
select the name it doesn't go to the sheet. If however, I
activate A1 (by entering the cell then pressing enter) it
works. Any way to change to code to do that?

-----Original Message-----
Hi Pat
assuming that your validation listbox is in cell A1 put the
following
code in the worksheet module of this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank


Pat wrote:
Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks


.

Another highly likely possibility is that you did not change A1 in the macro
to the cell containing the macro. Assuming your data validaton list is in
cell C9, then the macro would look like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C9")) Is Nothing Then Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Notice how A1 in the original has been changed to C9 in this version - above
the note from Frank to

'change this cell reference to your needs

meaning change the A1 or C9 to reflect the address of the cell that contains
the data validation. That is assuming you are using a version of Excel
later than xl97.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
I've tried this and it doesn't completely work. When I
select the name it doesn't go to the sheet. If however, I
activate A1 (by entering the cell then pressing enter) it
works. Any way to change to code to do that?

-----Original Message-----
Hi Pat
assuming that your validation listbox is in cell A1 put
the following
code in the worksheet module of this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
'change this cell reference to your needs
On Error GoTo CleanUp:
With Target
If .Value < "" Then
Worksheets(.Value).Activate
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank


Pat wrote:
Hi,

I have a drop down box (validation) linked to a list of
names on the same worksheet.

What I want is that when somebody goes to the drop down
list and picks a name they are taken to a worksheet with
that name that already exists in the worksheet.

Can someone help?

Thanks


.