Data Validation error ??
 

Hi

I have a worksheet with a validation list populating cells aa5:aa20, the
actual 'drop down list' is in cell G34 but when I select an item fromn this
list I can't get the worksheet relating to the selected item to open. I have
checked to make sure all the worksheets and items on the list are exactly the
same, but with no results.

here is my code

Dim r As Range

SheetName = Range("G34")

'Set the range for the for checking changes to A1:A1.
Set r = Intersect(Range("G34:G34"), 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

Can anybody offer any help - thanks in advance
Anthony

Hi Anthony

this should work for you
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$G$34" And Not IsNull(Target.Value) Then
Sheets(Target.Value).Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
Cheers
JulieD

"Anthony" wrote in message
...
Hi

I have a worksheet with a validation list populating cells aa5:aa20, the
actual 'drop down list' is in cell G34 but when I select an item fromn
this
list I can't get the worksheet relating to the selected item to open. I
have
checked to make sure all the worksheets and items on the list are exactly
the
same, but with no results.

here is my code

Dim r As Range

SheetName = Range("G34")

'Set the range for the for checking changes to A1:A1.
Set r = Intersect(Range("G34:G34"), 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

Can anybody offer any help - thanks in advance
Anthony