Excel VBA Code for Old Value New Value
 

Hi

I am struggling with VBA code to perform the following function (admittedly I am new to VBA).

I have 3 cells where users can change the value and the 4th cell is computed with a result like this:

CELL A1 - user input any value from 1 to 5
CELL A2 - user input any value from 1 to 5
CELL A3- user input any value from 1 to 5

CELL A4 computes =(A1+A2+A3)/3 and places the result there.

Once per month the user makes changes to any one or three cells (A1;A2:A3) and A4 computes the result.

At times the user may make no changes at all to these three cells, or perhaps he/she will change only one cell or two or even three cells values. Either way the result in A4 needs to be captures as "Previous Value or Old Value"

Heres where I need VBA help. I want Cell A5 to store the last or old value of A4 and in A6 I will use conditional formatting to display either up, down or no change arrows by looking at the New Value in A4 and the Old Value in A5

Please can you help me on this? In anticipation.

Excel VBA Code for Old Value New Value
 

Jemmeyson wrote:

I am struggling with VBA code to perform the following function
(admittedly I am new to VBA).

I have 3 cells where users can change the value and the 4th cell is
computed with a result like this:

CELL A1 - user input any value from 1 to 5
CELL A2 - user input any value from 1 to 5
CELL A3- user input any value from 1 to 5

CELL A4 computes =(A1+A2+A3)/3 and places the result there.

Just out of curiosity, why not just =AVERAGE(A1:A3) ?

Once per month the user makes changes to any one or three cells
(A1;A2:A3) and A4 computes the result.

At times the user may make no changes at all to these three cells, or
perhaps he/she will change only one cell or two or even three cells
values. Either way the result in A4 needs to be captures as "Previous
Value or Old Value"

Heres where I need VBA help. I want Cell A5 to store the last or old
value of A4 and in A6 I will use conditional formatting to display
either up, down or no change arrows by looking at the New Value in A4
and the Old Value in A5

Please can you help me on this? In anticipation.

The short answer: See the Worksheet_Change event.

The long answer: ...actually, I'll just link to my old logging code. I let
my site lapse so you get a Google Gropes link instead. Shrug.

https://tinyurl.com/AuricExcelLog

....which redirects to (watch the wordwrap):

https://groups.google.com/d/msg/micr...ming/roRHP_loX
xg/31Q6VDwkggAJ

The code there logs all changes made to a given workbook if placed in the
ThisWorkbook object, creating a new hidden log worksheet every time the book
is opened. (Make sure you include the edit I added afterwards or you might
not like the results.)

The code is public domain; feel free to use and/or modify as you see fit.
It's overkill for your needs, but what the hell. Note that the code expects
to be run on a 32-bit version of Office, since that's all I had at the time.

--
Cows would live a lot longer if they weren't made out of steak and leather.

Hi Auric_

Thank you for taking the time to reply to my query. I reviewed the code and felt overwhelmed :-) I dont think this will work for me, its far to complex. I require just one cell to be recorded before it changes to a new value. Is there a simpler way to achieve this?

Excel VBA Code for Old Value New Value
 

Jemmeyson wrote:

Thank you for taking the time to reply to my query. I reviewed the code
and felt overwhelmed :-) I dont think this will work for me, its far to
complex. I require just one cell to be recorded before it changes to a
new value. Is there a simpler way to achieve this?

The problem is, how do you decide when to capture the old value? There are 3
user inputs, and your A4 will update every time one of them is changed. So
let's say the user has to change two values:

A1 1 - 4 - 4
A2 1 - 1 - 4
A3 1 - 1 - 1
A4 1 - 2 - 3

The "old value" you want to capture at the end of this is 1, but in the
simplest cases, the captured value will be 2, since Excel counts this as 2
changes.

I suppose you could do this in the ThisWorkbook object:

Private oldVal, vals

Private Sub Workbook_Open()
'get initial values, before changes can be made
oldVal = Sheet1.Range("A4").Value
vals = Sheet1.Range("A1:A3").Value
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Exit Sub
Dim L0
For L0 = 1 To 3
If vals(L0, 1) < Sheet1.Cells(L0, 1).Value Then
Sheet1.Range("A5").Value = oldVal
Me.Save
Exit For
End If
Next
End Sub

This only updates the "old value" cell upon closing the workbook (and saves
the workbook if needed), but *only* if the workbook has already been saved.
(In other words, if you decide to close out without saving changes, this
doesn't bother checking previous values.)

There are likely better ways to do this, and there are certainly other
possibilities, e.g. you could add a level of complexity and keep a running
record of old values, with the value from the previous save (or whatever)
highlighted somehow, or you could add code to watch for user changes to A5
and update oldVal with that, etc.

--
Whoa, there, Captain Angry, take a deep breath.

Excel VBA Code for Old Value New Value
 

Hi Auric

I tried your VBA in a new blank worksheet and CELL A5 is not recording the old value from A4 regardless of Saving and Closing the workbook.

Any further thoughts on this?

Also, is there a way to dynamically see the change as opposed to closing and re-opening the sheet?

Excel VBA Code for Old Value New Value
 

Jemmeyson wrote:

I tried your VBA in a new blank worksheet and CELL A5 is not recording
the old value from A4 regardless of Saving and Closing the workbook.

Any further thoughts on this?

Make sure my code is in the ThisWorkbook object, not a module.

Also, is there a way to dynamically see the change as opposed to closing
and re-opening the sheet?

You could move the Workbook_BeforeClose code to Workbook_BeforeSave, but
you'd need to remove this line:

Me.Save

Then A5 will update every time you save.

--
The best sound in the world is the laughter of children.
Unless it's night and you live alone. Then it's the most terrifying sound.