Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110669 > unrolled thread

Excel VBA Code for Old Value New Value

Back to article view | Back to microsoft.public.excel.programming

Contents

  Excel VBA Code for Old Value New Value Jemmeyson <Jemmeyson.15490258@excelbanter.com> - 2018-05-21 23:34 +0100
    Re: Excel VBA Code for Old Value New Value "Auric__" <not.my.real@email.address> - 2018-05-22 00:13 +0000
      Re: Excel VBA Code for Old Value New Value Jemmeyson <Jemmeyson.154a29a8@excelbanter.com> - 2018-05-22 20:21 +0100
        Re: Excel VBA Code for Old Value New Value "Auric__" <not.my.real@email.address> - 2018-05-22 21:05 +0000
          Re: Excel VBA Code for Old Value New Value Jemmeyson <Jemmeyson.154cdab8@excelbanter.com> - 2018-05-24 20:53 +0100
            Re: Excel VBA Code for Old Value New Value "Auric__" <not.my.real@email.address> - 2018-05-25 09:03 +0000

#110669 — 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.




-- 
Jemmeyson

[toc] | [next] | [standalone]

#110670

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/microsoft.public.excel.programming/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.

[toc] | [prev] | [next] | [standalone]

#110671

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?




-- 
Jemmeyson

[toc] | [prev] | [next] | [standalone]

#110672

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.

[toc] | [prev] | [next] | [standalone]

#110675

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?




-- 
Jemmeyson

[toc] | [prev] | [next] | [standalone]

#110677

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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web