Trouble with event changes

[moneybydallas]

I am new to VBA and have been following many different YouTube videos. I have had great success until it came to event changes. In a specific worksheet I have entered the following:


Sub addition()


Cells(3, 1) = Cells(2, 1) + Cells(1, 1)

End Sub
Private Sub commandbutton_click()


Call addition


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


Call addition


End Sub

The first two work with no issue, however nothing I try in the last one will work. I am working on a much larger macro for work and I need this function to work. Can anyone help??

Dallas

 

[Peter_SSs]

The first two work with no issue, however nothing I try in the last one will work.

Welcome to the MrExcel board!

It works for me.

What evidence is there that it isn't working?
- error message when something is changed on the worksheet?
- cell A3 remains blank when something changes on the sheet, even though A1 and A2 contain numbers?
- something else?

Two things to check initially:
1. Do you have the Worksheet_Change code in the correct place? That is, in the sheet's module for the sheet that it is monitoring change on?

2. Make sure your 'Events' haven't become disabled. Put the following in a standard module and run it.

Sub EE()
  Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

This script will run anytime you make any sort of change to the sheet you insert it into.

Private Sub Worksheet_Change(ByVal Target As Range)
Call addition
 End Sub

But this sort of script can as written put you in a endless loop. Depending on what the script "addition" does
I would read up more on sheet event scripts before using this script.



You install it like this:

To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

 

[moneybydallas]

Hi Peter,

Thank you so much for responding. I think it was just disabled. Once I run your code, everything worked. Thank you very very much!

 

[Peter_SSs]

Hi Peter,

Thank you so much for responding. I think it was just disabled. Once I run your code, everything worked. Thank you very very much!

You are welcome.

Just emphasising what MAIT has stated about endless loops..

Your code is triggered when a change is made on your sheet.
Your current code then sums two values and enters the result on the sheet.
This entry on the sheet will trigger the Worksheet_Change code again.
Which will again call the code that sums the two values and enters the result on the sheet.
Even though the actual number in A3 may not have changed, this re-entering it on the sheet will trigger the W_C code again
etc
etc

You need to read up on (or search the forum for) Application.EnableEvents = False/True to avoid such loops.