How do I Use Worksheet_change(ByVal target as range)

No Comment   Very simple program.

Hello

right-click the sheet tab, click View Code and paste the following code into the code window

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("H4")) Is Nothing Then
    Range("O9:AA48").ClearContents
End If

End Sub

Select allOpen in new window

cheers, teylyn

I would suggest the following...

Dim PrevValue As String

Private Sub Worksheet_Activate()
    PrevValue = Range("h4").Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("H4") <> PrevValue Then Range("O9:AA48").ClearContents
End Sub

Select allOpen in new window

@Teylyn: The code that you gave is damn kool but I noticed that if I press F2 to get into the cell and came out 'without' changing anything, it still cleared the cell contents...

Sorry a slight change

Dim PrevValue As String

Private Sub Worksheet_Activate()
    PrevValue = Range("h4").Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("H4") <> PrevValue Then Range("O9:AA48").ClearContents
    PrevValue = Range("h4").Value
End Sub

Select allOpen in new window

Sid, you may want to educate the asker where to put that code. For a newbie, it will not be obvious.

Also, I wonder why you chose the selection change event? That's a lot of overhead if the goal is to monitor change for just cell H4.

As usual Excellent point and suggestion teylyn :)

New code

Dim PrevValue As String

Private Sub Worksheet_Activate()
    PrevValue = Range("h4").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("H4") <> PrevValue Then Range("O9:AA48").ClearContents
    PrevValue = Range("h4").Value
End Sub

Select allOpen in new window

hwibye: A snapshot is attached in case you are not aware where to put the code. Please ignore it if you already do know.

Sid
untitled.bmp

My initial post has been somewhat swamped

While there is some ambiguity as to whether change means change value (which may merit Sid's value storage), Events should be disabled when they will otherwise re-call themselves. In this case clearing the O range will re-call the Change event

In this case clearing the O range will re-call the Change event

Absolutely brettdj. However,

If Range("H4") <> PrevValue... will ensure that it doesn't go into endless loop...

Sid

True, but it's still better practice to disable events.

Point taken rorya :)

Hello  everybody !

i am the one who asked and I know a little bit about Excel programming so I put the 3 lines from tevlyn in my test program and it worked butifully in my test program,    now I am to put it into the real program with an other
Private sub  worksheet_calculate()   routine.  
It was simple and efficient.

Thanks to all of you for your engagement it was really encouraging.  I will be back later with other questions.

Regards
Hwibye
Norway

hwibye: Did you see my first post?

Sid

Sorry 2nd Post?

Sid

Hi Sid

Yes I  have seen your second post and iif the  3 lines from tevlyn  does not work  in my chart I will try it.

Thanks for your help,  I am impressed !!

Thanks
Harald

Harald, the point is if the user types anything in the cell H4 and presses 'Escape', without making any changes, do you want the data to be cleared in the range "O9:AA48" especially when you are interacting with a chart? If 'yes' then go either with Dave's or Teylyn's code else use the code that I gave in my 3rd post from top.

Sid

Hi Sid

This is an Excel sheet for horse racing.  I start it and it places bets on horses according to my criteria.
Races are uploaded 1 min before start automatically.

Cell H4  is the name of the race so what I want is to  clear  Max ods, Min odds, Matched odss etc when the name of the new race is placed in H4 by Bet Angel program which intracts with Betfair through API.  Later I will use a robot direct towards Betfair.

Thanks
Harald

> i am the one who asked and I know a little bit about Excel programming so I put the 3 lines from tevlyn in my test program a

Without wanting to sound churlish my initial post preceded this, it was on the same basis and it included the disabling of events.

Regards

Dave