Trouble with event changes

moneybydallas

New Member
Joined
Feb 28, 2016
Messages
3
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,130
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
Sub EE()
  Application.EnableEvents = True
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
This script will run anytime you make any sort of change to the sheet you insert it into.
Code:
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

New Member
Joined
Feb 28, 2016
Messages
3
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,130
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,148
Messages
5,657,102
Members
418,355
Latest member
michaelirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top