Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 21st 18, 11:34 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Exclamation 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.

  #2   Report Post  
Old May 22nd 18, 01:13 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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.
  #3   Report Post  
Old May 22nd 18, 08:21 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Default

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?
  #4   Report Post  
Old May 22nd 18, 10:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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.
  #5   Report Post  
Old May 24th 18, 08:53 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Default 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?


  #6   Report Post  
Old May 25th 18, 10:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating excel file, adding code to it from code, VBE window stays BlueWolverine Excel Programming 0 November 5th 09 07:55 PM
while my c# code running ,clicks on excel document interrupts code Kayıhan Excel Programming 2 April 8th 09 11:54 AM
Excel code convert to Access code - Concat & eliminate duplicates italia Excel Programming 1 September 12th 06 12:14 AM
stubborn Excel crash when editing code with code, one solution Brian Murphy Excel Programming 0 February 20th 05 05:56 AM
Excel XP VBA code to search all macro code in Excel module for specific search string criteria Ed[_18_] Excel Programming 4 May 20th 04 02:08 PM


All times are GMT +1. The time now is 09:15 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ©2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017