Worksheet event help

[K1KKKA]

Have the following in a sheet code that i would like to activate when
the enter key is pressed in B5.

tried the following, but no success, the 1st part i was hoping would be
the change event that would run a sorting macro in the 2nd part. any
help???


(1st Part)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(0, 0) = "b5" Then




(2nd Part)

Application.ScreenUpdating = False
ActiveWindow.FreezePanes = False
Range("A4").Select
ActiveWindow.SmallScroll Down:=234
Range("A4:B273").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
Sheets("LookupLists").Select
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E300")
Range("E2:E300").Select
Sheets("Master").Select
ActiveWindow.SmallScroll Down:=-246
Range("A4").Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating = True
End If
End Sub

 

[Dave Peterson]

Unless you have
Option Compare Text
at the top of the module, this line:
If Target.Address(0, 0) = "b5" Then
will never have the True portion followed.

Try:
If Target.Address(0, 0) = "B5" Then

Personally, I like this test better:

if target.cells.count > 1 then exit sub
if intersect(target, me.range("b5")) is nothing then exit sub

I find it easier to change (to include more cells, for example).

(I didn't look any further.)

 

[K1KKKA]

Dave

Thanks

if target.cells.count > 1 then exit sub
if intersect(target, me.range("B5")) is nothing then exit sub

Worked a treat.
Never even thought about text case, appreciate your assistance and
comments



Steve

 

[Erich Neuwirth]

Personally,
I find coding like

If (Target.Row = 5) and (Target.Column = 2) Then

less error prone