VBA not updating when cell values are changed

That's the basic procedure - you now need a way to run it...

Open the VBA editor and double click the Sheet in the project tree to open the code window. In the dropdown on the right hand side above the code window select 'Change'. You should then see a procedure stub like

Please Login or Register  to view this content.

This procedure will run whenever that sheet is changed so you can call your other procedure, or copy it into this procedure... your call.

This is only the start, you also need to check if either of cells F5 and F19 were edited before calling the procedure, but trying adding a bare message box into the blank procedure above and check you can see the message when you edit any cell

This sub will run only if you explicitly call it. It will not happen automatically.

If you want something to happen automatically when a cell is changed, you need to use the Change event. In the module for the worksheet that contains this data, use this code (red indicates what I changed from your original code). Also you have undeclared variables. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Also I recommend indentation to show the control structure.

Please Login or Register  to view this content.

Hi Cytop, thank you for your help.

However, this happens:

screen.jpg

No Variable declarations...
Can see this going pear-shaped...I suggest uploading a sample file and explaining exactly what it is you are wanting to achieve...
See top yellow banner

Originally Posted by cytop

Can't see- get an invalid attachment message.

I fixed it.

Please replace your code with what is in post #3 and try that.

By the way, the code in your file is not the same as what you posted.

F5 and F19 are formulas. The Change event does not occur when the result of a formula changes, only if the actual content of a cell changes.

Also, F5 and F19 are sums of the rows above. It looks like you plan on inserting new rows, so those totals will not be in F5 or F19 after you insert a new row; they'll be in F6 or F20, for example. You have the same issue with H9.

One way to do this is to make these totals named ranges, and keep a copy of their last values on another sheet. Then monitor the Calculate event, and compare the values every time there is a calculation on the sheet. If that values don't match, make your changes then update the "last value". I'm not 100% sure that will work for you because I don't know how you plan to use this. But I have made those changes in your file.

Also, I don't understand your big picture here, but you seem to be hard-coding some dates in your VBA. It appears that you will have to update your code every month. There's a better way to do that but I don't know what you're doing so I can't offer a solution just yet. What kind of updates will you be making to this file and when?

What you are doing is assigning a value to a variable. Declaring a variable is using the Dim statement to create the variable and indicate its data type. If you do not do this, then VBA will assume that every variable that is not declared is a Variant type. This can cause a lot of bugs if you misspell a variable name in your code. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

If you solved your own problem, please provide your solution so that somebody seeing this thread in a search for the same problem will see how it was solved.