gibbo1715
02-09-2005, 02:11 AM
Can you have a look at the code below, it does work but im not happy with my logic
What i have done is in the Worksheet_SelectionChange event set it up so that if a certain cell (Range A3:A100) matches another worksheet it will take you there ( Bit like a hyperlink).
My problem is that the Worksheet_SelectionChange code i ve written below runs no matter which cell is selected, i ve got around this with the exit sub in the error handling but how do I get it to run the loop only if a cell between A3 and A100 is selected so I can give my users a message?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Dim cel As Range
Dim Name As String
On Error GoTo Error
Name = ActiveCell.Value
Set Rng = Range("A3", Range("A110"))
For Each cel In Rng
If cel.Value = Name Then
Sheets(Name).Select
Exit Sub
End If
Next
Exit Sub
Error:
'MsgBox "Error"
Exit Sub
End Sub
What i have done is in the Worksheet_SelectionChange event set it up so that if a certain cell (Range A3:A100) matches another worksheet it will take you there ( Bit like a hyperlink).
My problem is that the Worksheet_SelectionChange code i ve written below runs no matter which cell is selected, i ve got around this with the exit sub in the error handling but how do I get it to run the loop only if a cell between A3 and A100 is selected so I can give my users a message?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Dim cel As Range
Dim Name As String
On Error GoTo Error
Name = ActiveCell.Value
Set Rng = Range("A3", Range("A110"))
For Each cel In Rng
If cel.Value = Name Then
Sheets(Name).Select
Exit Sub
End If
Next
Exit Sub
Error:
'MsgBox "Error"
Exit Sub
End Sub