PDA

View Full Version : [SOLVED] Untidy Worksheet_SelectionChange code



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

Jacob Hilderbrand
02-09-2005, 02:41 AM
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
02-09-2005, 02:42 AM
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
02-09-2005, 02:55 AM
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
02-09-2005, 03:12 AM
You're Welcome :beerchug:

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

johnske
02-09-2005, 03:57 AM
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) :thumb

Regards,
John :devil: