Simple code error..... I think

[picklefactory]

Hello folks, hoping for some help please?
A bit embarrassing, but then I am a newbie to VBA
I've made a very simple line of code just to empty one cell based on the contents of another....... YES, just one line of code, and I'm still struggling

It works, but I think it is looping or doing something else strange as it crashes Excel when it runs and I have to shut it down, I guess I'm missing something really basic here, but I dunno what I'm afraid.

Help???
Thanks

Code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("F16") = 1 Then Range("J16").ClearContents
End Sub

 

[Jonmo1]

The problem is it's an endless loop...

YOu change F16 - This triggers the code.
If F16 = 1, then it clears contents of J16
That changed the value of J16, therefor triggering the code again.
If F16 = 1, then it clears contents of J16
That changed the value of J16, therefor triggering the code again.
If F16 = 1, then it clears contents of J16
That changed the value of J16, therefor triggering the code again.
If F16 = 1, then it clears contents of J16
That changed the value of J16, therefor triggering the code again.

And so on...


Disable events to prevent this, then re-enable them at the end...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("F16") = 1 Then Range("J16").ClearContents
Application.EnableEvents = True
End Sub

Hope that helps...

 

[picklefactory]

jonmo1
Superb... thank you for such a rapid reply. Told you I'd missed something basic. I hate being a newbie
Thanks again, all OK now of course

 

[MrKowz]

What is happening is that every time you clearcontents on J16, the code is being called, since it is effectively a "worksheet change".

To avoid this, we can use a few options:

1. Turn off/on events:
   

   Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   If Range("F16") = 1 Then Range("J16").ClearContents
   Application.EnableEvents = True
   End Sub

2. Have the code run only when F16 is changed:
   

   Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("F16")) Is Nothing Then
       If Range("F16") = 1 Then Range("J16").ClearContents
   End If
   End Sub

3. Use a combination of 1 & 2:
   

   Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   If Not Intersect(Target, Range("F16")) Is Nothing Then
       If Range("F16") = 1 Then Range("J16").ClearContents
   End If
   Application.EnableEvents = True
   End Sub

 

[picklefactory]

MrKowz

Thanks to you also, you just don't type as quickly as jonmo1

Much appreciated.

 

[Jonmo1]

jonmo1
Superb... thank you for such a rapid reply. Told you I'd missed something basic. I hate being a newbie
Thanks again, all OK now of course

Glad to help, thanks for the feedback...


If you're using Event Code, you're not a Newbie anymore...

 

[picklefactory]

Glad to help, thanks for the feedback...


If you're using Event Code, you're not a Newbie anymore...

'ATTEMPTING' to use Event Code....... not too successfully, seemingly.

 

[Jonmo1]

'ATTEMPTING' to use Event Code....... not too successfully, seemingly.

Depends on your definition of success...

The code DID in fact successfully do what you wanted....
It was just doing it over and over again....

 

[MrKowz]

MrKowz

Thanks to you also, you just don't type as quickly as jonmo1

Much appreciated.

Thanks for the feedback!

 

[picklefactory]

Depends on your definition of success...

The code DID in fact successfully do what you wanted....
It was just doing it over and over again....

True

I'm better than I thought

Thanks folks, even as simple a thing as that is a worthwhile lesson for numpties of my level, I've learned a number of way of dealing with a few scenarios there.