Avatar of hwibye
hwibyeFlag for Norway asked on

How do I Use Worksheet_change(ByVal target as range)

I am looking to write the following program in Excel VBA  subroutine:


Private Sub Worksheet_Change(ByVal Target As Range)

      If Cell H4 changes then do the following:
     
             Range ("O9:AA48").ClearContents
End sub
Microsoft ExcelMicrosoft Applications

Avatar of undefined
Last Comment
Dave

8/22/2022 - Mon
ASKER
hwibye

No Comment   Very simple program.
ASKER CERTIFIED SOLUTION
Dave

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

Open in new window


cheers, teylyn
SiddharthRout

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

Open 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...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SiddharthRout

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

Open in new window

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Sid, you may want to educate the asker where to put that code. For a newbie, it will not be obvious.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SiddharthRout

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

Open 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
Dave

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
SiddharthRout

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rory Archibald

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

Point taken rorya :)
ASKER
hwibye

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SiddharthRout

hwibye: Did you see my first post?

Sid
SiddharthRout

Sorry 2nd Post?

Sid
ASKER
hwibye

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SiddharthRout

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
ASKER
hwibye

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
Dave

> 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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.