pop up message that tells you all the changes you made to a spreadsheet

[hopr37]

I use a lot of data entry on my spreadsheet.
Is there a way to create a message box that will tell me the entries that I made during the day?
I have 3 different cells that would need to be displayed.
the first cell ( cell A) is just a description
the second cell ( Cell B) is an amount that I input
the third cell ( Cell C) is an amount that is subtracted from the second cell.

So as an example if I made 3 changes I could hit a button and a message would pop up indicated the 3 cells and what was done with them.
"you have changed:
"description, amount, amount"
"description, amount, amount"
description, amount, amount"

and then reset if I close the worksheet.

Thank You

 

[Joe4]

Re: pop up message tht tells you all the changes you made to a spreadsheet

I would recommend adding VBA code that add a date/time stamp in some column when a row is updated (search "Excel VBA DateTime Stamp" to see lots of posts with that code).
Then, I think I would use Advanced Filters to filter the ones from today to a new sheet (can also be automated using VBA).

 

[jmacleary]

Re: pop up message tht tells you all the changes you made to a spreadsheet

What you describe is actually 9 changes, as it would be difficult to track unless you can guarantee that you will always make the 3 changes in 1 'go'. As a simple alternative that you could develop as needed, this code will track each cells change:

Put the following into the worksheet code area (right-click the sheet name and select View code then paste the code below)

Private Sub Worksheet_Change(ByVal Target As Range)
logger = logger + vbNewLine + Target.Address + " was " + PrevVal + " - now " + Target.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevVal = Target.Value
End Sub

You will also need these 2 declarations ans simple subroutine in a module (point your button at this macro):

Global logger As String
Global PrevVal As String

Sub showhist()
MsgBox logger

End Sub

 

[hopr37]

Re: pop up message tht tells you all the changes you made to a spreadsheet

Thank you for that. I created a button and placed the main code in my worksheet and the declarations in a module.
However, I get a blank pop up box.

 

[hopr37]

Re: pop up message tht tells you all the changes you made to a spreadsheet

hmmm.
Hit the button and getting "invalid attribute in sub or function"

Sub Button1_Click()
Global logger As String

 

[MickG]

Re: pop up message tht tells you all the changes you made to a spreadsheet

Have a play with this example:-
https://app.box.com/s/utkn4h3kifb3cw2rdtvdg6d9d8caepu0

 

[jmacleary]

Re: pop up message tht tells you all the changes you made to a spreadsheet

hmmm.
Hit the button and getting "invalid attribute in sub or function"

Sub Button1_Click()
Global logger As String

It looks like youve got the global inside the sub rather than at the top.

 

[hopr37]

Re: pop up message tht tells you all the changes you made to a spreadsheet

It works. ( I was moving things around and I had the same info in two different spots)
However, i still get a blank popup with no info.

 

[jmacleary]

Re: pop up message tht tells you all the changes you made to a spreadsheet

Oh dear. I'll explain the code and see if that helps you find where the problem is. The Worksheet_SelectionChange code just catches the current value of the cell you have just clicked on. The Worksheet_Change code then appends the cell address, before and after values to the logger variable. Then the button code just displays the logger data. I suspect the global definitions may not be in the right place. You could set a break point on the 2 end subs in the worksheet code and make sure they are being executed. At that point, you can see what the logger variable is set to. I hope this helps, but if not then i'm a bit stumped since it works fine in my test workbook.

 

[hopr37]

Re: pop up message tht tells you all the changes you made to a spreadsheet

can't set breakpoints and no matter where I place the globals I still get a blank message box