Terry from Springfield, MO asks about a quicker, one-click way to change the Move Selection After Enter direction. Episode 1071 show you how to solve this problem by recording a couple of quick macros.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen, oh we're back here in Excel 2007, I spent last week talking about Excel 2010.
Great question today sent in by Terry from Springfield Missouri.
Terry is a sponsored a number of seminars out in Springfield, and so when this question came in I said this is a great question because it deals with how to do a simple little MACRO and put it in your personal MACRO Workbook.
Terry says; hey I really like you know if we type 123 and press ENTER it goes down, and in my books and in the seminars I talk about how we can change that.
Go into Excel options, go back to advanced and we can change that to be RIGHT.
Click OK.
That's now I press 123 and it goes to the right.
Terry says I would like a ONE-CLICK way to change from right to down, and I said all right we can do that.
But we're going to do that with a MACRO stored in your personal MACRO Workbook, so let's do this.
We go to VIEW, we go to MACROS, RECORD A MACRO, and I'm going to say that it is the MoveDown.
I'm not going to assign it to a shortcut key, leave it in the personal MACRO workbook.
Click OK, now while THE MACRO RECORDER is recording, I'm going to go to Excel options, go to ADVANCED and change this to DOWN.
Good enough, click OK.
Now we want to stop recording, so we come down here to the stop recording button.
All right, first MACRO is done.
Let's do that again, MACROS, RECORD MACRO.
This one is we called MoveRight, click OK.
Back to Excel options, back to ADVANCED, and change this to RIGHT.
Click OK and then stop.
All right, so now we have our two MACROS, but we need a good way to run those and we're going to add them to the QUICK ACCESS TOOLBAR.
Now your QUICK ACCESS TOOLBAR might be up here next to the Office button, or you might have moved it down below the ribbon.
Either way, RIGHT CLICK or choose customize, and then in the customized dialog box we're going to go to MACROS and find our MACRO, so there's MoveDown and MoveRight.
I'm going to add both of those to the QUICK ACCESS TOOLBAR.
I'm going to customize them both, let's see here we're going to modify this.
We have 144 different icons and beautifully move down, there is a down arrow.
Excellent and we even change the tooltip to say DOWN AFTER ENTER, click OK and then on MoveRight, we'll modify that one and use the right arrow.
Tooltip will be RIGHT AFTER ENTER, click OK.
All right, now here's the real test; so we're going to choose DOWN go 123, press Enter, 123 Enter, and 123 Enter.
Will choose RIGHT, so 123 Enter, 123 Enter, there you have it, a setting that generally would take you a couple of mouse clicks if it's something that you're switching back and forth for all the time.
The MACRO RECORDER will do a great job for you, very easy to record, a couple of very quick they're really one line macros and then assign those two buttons on THE QUICK ACESS TOOLBAR.
So now you have that access, so there you have it.
Thank Terry for sending in that great question to get us started back on our week returning to Excel 2007, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen, oh we're back here in Excel 2007, I spent last week talking about Excel 2010.
Great question today sent in by Terry from Springfield Missouri.
Terry is a sponsored a number of seminars out in Springfield, and so when this question came in I said this is a great question because it deals with how to do a simple little MACRO and put it in your personal MACRO Workbook.
Terry says; hey I really like you know if we type 123 and press ENTER it goes down, and in my books and in the seminars I talk about how we can change that.
Go into Excel options, go back to advanced and we can change that to be RIGHT.
Click OK.
That's now I press 123 and it goes to the right.
Terry says I would like a ONE-CLICK way to change from right to down, and I said all right we can do that.
But we're going to do that with a MACRO stored in your personal MACRO Workbook, so let's do this.
We go to VIEW, we go to MACROS, RECORD A MACRO, and I'm going to say that it is the MoveDown.
I'm not going to assign it to a shortcut key, leave it in the personal MACRO workbook.
Click OK, now while THE MACRO RECORDER is recording, I'm going to go to Excel options, go to ADVANCED and change this to DOWN.
Good enough, click OK.
Now we want to stop recording, so we come down here to the stop recording button.
All right, first MACRO is done.
Let's do that again, MACROS, RECORD MACRO.
This one is we called MoveRight, click OK.
Back to Excel options, back to ADVANCED, and change this to RIGHT.
Click OK and then stop.
All right, so now we have our two MACROS, but we need a good way to run those and we're going to add them to the QUICK ACCESS TOOLBAR.
Now your QUICK ACCESS TOOLBAR might be up here next to the Office button, or you might have moved it down below the ribbon.
Either way, RIGHT CLICK or choose customize, and then in the customized dialog box we're going to go to MACROS and find our MACRO, so there's MoveDown and MoveRight.
I'm going to add both of those to the QUICK ACCESS TOOLBAR.
I'm going to customize them both, let's see here we're going to modify this.
We have 144 different icons and beautifully move down, there is a down arrow.
Excellent and we even change the tooltip to say DOWN AFTER ENTER, click OK and then on MoveRight, we'll modify that one and use the right arrow.
Tooltip will be RIGHT AFTER ENTER, click OK.
All right, now here's the real test; so we're going to choose DOWN go 123, press Enter, 123 Enter, and 123 Enter.
Will choose RIGHT, so 123 Enter, 123 Enter, there you have it, a setting that generally would take you a couple of mouse clicks if it's something that you're switching back and forth for all the time.
The MACRO RECORDER will do a great job for you, very easy to record, a couple of very quick they're really one line macros and then assign those two buttons on THE QUICK ACESS TOOLBAR.
So now you have that access, so there you have it.
Thank Terry for sending in that great question to get us started back on our week returning to Excel 2007, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
