John asks a common question: When I enter a value in B, can you immediately add the value to C and clear out B for the next entry? While this can be done, the loss of Undo makes it risky. I show a VBA macro to do immediately and also to sweep a batch of entries.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1973 - Automatically Add Daily Entries for Running Total!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question is via Twitter from John, John has three columns, he has daily entries and total, he says "Every day, I'd like to have the cost of the items of these things into 'Daily Entries'.
It t will immediately add those values to the pre-existing total, and then will remain blank for the entry for the next time." Alright.
And I tweeted back to say: "Hey, you know, it's immediately, immediately it's possible, but, it's lousy if you ever make a mistake, because it knocks out undo." So I'm going to show you two ways, immediately, and the way that I would do this.
Alright, to have these entries immediately get added over here, we start out, first make sure the workbook is saved as an xlsm, so a File, Save As, and you have to choose xlsm or xlsb, the default xlsx will not work.
Then we're going to go into VBA, so that's Alt+F11, if you don't see the Project Explorer, press CTRL+R for the Project Explorer, we're on sheet 1, I'm going to right-click and say View Code.
Then, two dropdowns, from the top left drop-down, only one choice, Worksheet, they automatically put in SelectionChange, I want to change that to be Change, in other words, every time that we change something on this worksheet, we're going to run this macro code.
And what we do, they tell us what cell or cells were just changed, I check and see if it's an intersection of the place where we're going to be entering data, in this case is B2:B15, in your case, if you had larger or smaller, you would change that.
If they changed something outside that range, then we don't do anything.
Otherwise, for each cell that they changed, we're going to say that the cell 1 column, to the right of this cell, is going to be going to whatever was there before, plus that value we just typed.
And then clear out the original cell.
Now, before we change anything in the worksheet, we have to turn off events, and then turn them back on.
Alright?
So this little bit of code, File, Close, We return to Microsoft Excel.
We'll do what I think John was wanting to do.
So here, Eggplant, we used to have 4, type in 3, press Enter, and we get 7.
These two, we both had 9 I type in 2, and then CTRL+Enter, and they both get incremented by 2.
So I can handle the situation where they select multiple items.
But here's a problem, let's say that I want to add one Kale, and I accidentally type 11, and then press Enter.
Oh, shoot!
Undo?
No, there's no Undo!
Having a selection change macro, or actually any macro, wipes out undo.
Now you have to go back and see: Now what did I do?
I meant to enter 1, I entered 10 or 11, so I have to do -10.
Alright?
What a horrible pain that is to do.
So what I would prefer, I'm going to come here to Method2, is, telling people that they have to go through and enter all their values for the whole day.
And if you make a mistake, I'll mention that too...
Alright, then when that's completely done, we're going to move those entries over to the total.
Here's how you do that: Alt+F11, insert a module, now it's on Module1.
Simple little macro, I call it: Sweep for each cell in B2:B15!
Again we change that, add the value in this cell to the value that was to the right of us, and then clear the whole range.
How do we get them to run that macro, just insert any shape, I like the rounded rectangle, type a caption there, we can center, maybe even make a little bit larger, and then right click, Assign Macro, and we're assigning it to the Sweep macro.
Alright so now, here's all these entries, choose Add Daily Entries to Total, and they all get moved over at once.
That way you can make sure that you didn't make any mistakes.
So, can we do it immediately?
Yes, you can, but it'd be a lot safer, in my opinion, to have a macro that would do it all at one time.
All right so hey I want to thank John for sending that question in.
And hey, if you're interested in learning more about VBA, check out this book by Tracy and myself, we'll walk you up the VBA learning curve.
Anyway, see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question is via Twitter from John, John has three columns, he has daily entries and total, he says "Every day, I'd like to have the cost of the items of these things into 'Daily Entries'.
It t will immediately add those values to the pre-existing total, and then will remain blank for the entry for the next time." Alright.
And I tweeted back to say: "Hey, you know, it's immediately, immediately it's possible, but, it's lousy if you ever make a mistake, because it knocks out undo." So I'm going to show you two ways, immediately, and the way that I would do this.
Alright, to have these entries immediately get added over here, we start out, first make sure the workbook is saved as an xlsm, so a File, Save As, and you have to choose xlsm or xlsb, the default xlsx will not work.
Then we're going to go into VBA, so that's Alt+F11, if you don't see the Project Explorer, press CTRL+R for the Project Explorer, we're on sheet 1, I'm going to right-click and say View Code.
Then, two dropdowns, from the top left drop-down, only one choice, Worksheet, they automatically put in SelectionChange, I want to change that to be Change, in other words, every time that we change something on this worksheet, we're going to run this macro code.
And what we do, they tell us what cell or cells were just changed, I check and see if it's an intersection of the place where we're going to be entering data, in this case is B2:B15, in your case, if you had larger or smaller, you would change that.
If they changed something outside that range, then we don't do anything.
Otherwise, for each cell that they changed, we're going to say that the cell 1 column, to the right of this cell, is going to be going to whatever was there before, plus that value we just typed.
And then clear out the original cell.
Now, before we change anything in the worksheet, we have to turn off events, and then turn them back on.
Alright?
So this little bit of code, File, Close, We return to Microsoft Excel.
We'll do what I think John was wanting to do.
So here, Eggplant, we used to have 4, type in 3, press Enter, and we get 7.
These two, we both had 9 I type in 2, and then CTRL+Enter, and they both get incremented by 2.
So I can handle the situation where they select multiple items.
But here's a problem, let's say that I want to add one Kale, and I accidentally type 11, and then press Enter.
Oh, shoot!
Undo?
No, there's no Undo!
Having a selection change macro, or actually any macro, wipes out undo.
Now you have to go back and see: Now what did I do?
I meant to enter 1, I entered 10 or 11, so I have to do -10.
Alright?
What a horrible pain that is to do.
So what I would prefer, I'm going to come here to Method2, is, telling people that they have to go through and enter all their values for the whole day.
And if you make a mistake, I'll mention that too...
Alright, then when that's completely done, we're going to move those entries over to the total.
Here's how you do that: Alt+F11, insert a module, now it's on Module1.
Simple little macro, I call it: Sweep for each cell in B2:B15!
Again we change that, add the value in this cell to the value that was to the right of us, and then clear the whole range.
How do we get them to run that macro, just insert any shape, I like the rounded rectangle, type a caption there, we can center, maybe even make a little bit larger, and then right click, Assign Macro, and we're assigning it to the Sweep macro.
Alright so now, here's all these entries, choose Add Daily Entries to Total, and they all get moved over at once.
That way you can make sure that you didn't make any mistakes.
So, can we do it immediately?
Yes, you can, but it'd be a lot safer, in my opinion, to have a macro that would do it all at one time.
All right so hey I want to thank John for sending that question in.
And hey, if you're interested in learning more about VBA, check out this book by Tracy and myself, we'll walk you up the VBA learning curve.
Anyway, see you next time for another netcast from MrExcel!
