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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,137
Office Version
365
Platform
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
16,762
Office Version
2013
Platform
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
45,137
Office Version
365
Platform
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,102,153
Messages
5,485,059
Members
407,480
Latest member
breederbulldog

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top