Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
data validation on sth else | Excel Worksheet Functions |