Untidy Worksheet_SelectionChange code

[gibbo1715]

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

[Jacob Hilderbrand]

See if this helps you get started.

If Intersect(Target, Range("A3:A110")) Is Nothing Then
    MsgBox "The selection is not in the required range"
Else
    MsgBox "Ok, that selection is fine, now let's do something"
End If

[johnske]

Any use to you?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3:A100")) Is Nothing Then
Exit Sub
Else
On Error Resume Next '< error is empty cell or sheet doesn't exist
Sheets(ActiveCell.Value).Select
End If
End Sub

[gibbo1715]

both options will do what i want in different ways

I ll be glad when i ve finally managed to get my head fully around VBA and excel

Once again solved my problem

Thankyou to you both

[Jacob Hilderbrand]

You're Welcome

You can mark your own threads Solved. Above the first post click on Thread Tools and select Mark Solved, then Perfom Action.

[johnske]

Hi Gibbo,

Not a prob, but, I think you'll find it is a little different to your original. If you remove "On Error Resume Next" you'll see it only fires when a selection is made in the range you gave... (ps you can also change ActiveCell to Target if you like)

Regards,
John