"Worksheet_SelectionChange" in Excel 2

[LeighAnne]

Please see the attached code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$N$206" Then Range("$A$206&quot.Select
End Sub

The code is attached to an excel 97 worksheet. When the user selects cell N206, I want to move their selection to A206.

The code doesn't run at all, regardless of which cells I select. Does anyone have any idea what I am doing wrong?

 

[VulcanJedi]

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveCell = Cells(206, 14) Then Cells(206, 1).Select
End Sub Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates the threads / posts for others!

 

[acron]

There is nothing really wrong with your code and it should run. It is posible that somehow you Application.EnableEvents has been set to False.

Try using Application.EnableEvents = True from the immediate window and test the code again.

A.C.

 

[DaleWatson123321]

LeighAnne,

I managed to get the following to work...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = Range("keycell&quot Then
Range("A206&quot.Select
End If
End Sub

If you would prefer to (always) have cell "A206" in the "top-left-corner" of the active screen, then you can replace: Range("A206&quot.Select
with: Application.Goto Range("A206&quot, True

The above requires that you assign a range name in a cell somewhere off-to-the-side on the same worksheet. I've named the cell "keycell", but you can use whatever name you like, so long as you also use the same name in the SelectionChange event.

In the cell named "keycell", enter the address of the cell in question: i.e. enter: $N$206. Then, you might want to color the cell WHITE to hide it from view.

There may be other options, like the previous one posted, but I couldn't manage to get it to work.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

LeighAnne,

Just want to second what Acron suggested. I pasted your code into a

Worksheet_SelectionChange

event procedure and it worked fine. If setting EnableEvents = True does the job, I would look for other code where this is set to False and make sure there is a complementary statement returning this to true. This property is not automatically re-set.

Regards,
Mike

 

[LeighAnne]

Thanks for al your help everyone !

Acron was right! My Application.EnableEvents was set to false.

Thanks again,

Leigh-Anne

 

[LeighAnne]

One more thing....

Now I have my Target.Address, is there a way using code that I can check that the Target.Address cell is within a specific range, say $N$206:$P$209 ?

Any help would be most graciously accepted !

Leigh-Anne

 

[xlbo]

Set isect = Application.Intersect(Range(target.address), Range("N206209&quot)
If isect Is Nothing Then
MsgBox "Target Cell NOT in N206209"
Else
MsgBox "Target Cell is in N206209"
End If

should do the trick Rgds
~Geoff~