Worksheet event help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

K1KKKA - 18 Jan 2007 22:08 GMT

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

Reply to this Message

Dave Peterson - 18 Jan 2007 22:29 GMT

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.)

> Have the following in a sheet code that i would like to activate when
> the enter key is pressed in B5.
[quoted text clipped - 31 lines]
> End If
> End Sub

Signature

Dave Peterson

Reply to this Message

K1KKKA - 18 Jan 2007 22:44 GMT

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

> Unless you have
> Option Compare Text
[quoted text clipped - 49 lines]
> > End If
> > End Sub

Reply to this Message

Erich Neuwirth - 19 Jan 2007 19:39 GMT

Personally,
I find coding like

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

less error prone

> Unless you have
> Option Compare Text
[quoted text clipped - 6 lines]
>
> Personally, I like this test better:

Reply to this Message