Today's Podcast focuses on a question that Mark asks about a trick that not many people know: You can select all of a Formula's Precedent Cells using the Mouse or Keyboard. Yet, there doesn't seem to be an easy way to return to the Original Cell using just the Mouse. In Episode #1719, Bill shows us how to Select Precedents with the Keyboard *or* the Mouse; how to go back to the Original Cell with the Keyboard, and a One-line Macro to go back to the Original Cell using the 'Cringe-Worthy' SendKeys method in VBA.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1719 - Return from Select Precedents!
Hey, welcome back to the MrExcel netcast.
Today's question in sent in by Mark.
Talks about using an old little trick that I first learned from my friend Howard, up in New York City, where you can turn off "Allow editing directly in cells" and double click a cell, instead of putting in an Edit mode.
It shows you all of the precedents, but first let's talk about the keyboard method for doing this.
So we have this cell here which is referring to several other cells, if you press Ctrl+[ it will select all of those cells.
And then Mark goes on to say, the only way that he's found to get back is to press F5 and then Enter to go back to the original cell.
And he's trying to figure out a way, since he already has the mouse in his hand, to have a one-click way to go back.
Alright, well first let's talk about the keyboard way.
Maybe we can just get Mark to stop using the mouse.
If you do Ctrl+[ , it will select the precedence, and then Ctrl+ ] will go back to the original cell.
So there we have a easy keyboard method to select the precedence and then go back, but let's say we want to do this with a mouse.
So File, Options, Advanced, uncheck this box "Allow editing directly in cells", click OK.
Now when we double click the cell, so you're using the mouse, double click the cell, it selects all the precedents, and you can even go to the keyboard and press Enter to loop through each of those precedents.
But, to go back, F5 and then click OK, so is there some way to write a macro that will do F5, OK?
So we're going to switch over to VBA here, AlT+F11, and I wrote this tiny little macro, I called it GoBack, and we're using a feature that is much maligned, because it is not reliable.
But here we're just doing two keys, and I've tested a few times, that's called Application.SendKeys, and that in quotes we're sending an F5, that has to be in {}, and then an Enter.
So I'm going to switch back.
Once you've added this, we're going to add a little function to our Quick Access Toolbar.
Right, click Customize Quick Access Toolbar, look under Macros, and the GoBack macro.
You can change that icon of course, but I'll just click OK.
And so we'll double click the cell, select the precedence, and now click the GoBack to go back.
Boy, that was so fast.
On a slower computer you can actually see the Go To dialog box up there.
So a couple of cool tricks that most people don't know about, Ctrl+[ to select all the precedents, Ctrl+ ] to go back.
If you're a mouse person, you have to turn off the "Allow editing directly in cells" that way the double click will allow you to select it.
And then of course, either Ctrl+ ], or if you want to continue to use the mouse, the little GoBack macro.
Alright well hey, I want to thank Mark for sending that question in, I want to thank you for stopping by, we'll you next time for another Netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1719 - Return from Select Precedents!
Hey, welcome back to the MrExcel netcast.
Today's question in sent in by Mark.
Talks about using an old little trick that I first learned from my friend Howard, up in New York City, where you can turn off "Allow editing directly in cells" and double click a cell, instead of putting in an Edit mode.
It shows you all of the precedents, but first let's talk about the keyboard method for doing this.
So we have this cell here which is referring to several other cells, if you press Ctrl+[ it will select all of those cells.
And then Mark goes on to say, the only way that he's found to get back is to press F5 and then Enter to go back to the original cell.
And he's trying to figure out a way, since he already has the mouse in his hand, to have a one-click way to go back.
Alright, well first let's talk about the keyboard way.
Maybe we can just get Mark to stop using the mouse.
If you do Ctrl+[ , it will select the precedence, and then Ctrl+ ] will go back to the original cell.
So there we have a easy keyboard method to select the precedence and then go back, but let's say we want to do this with a mouse.
So File, Options, Advanced, uncheck this box "Allow editing directly in cells", click OK.
Now when we double click the cell, so you're using the mouse, double click the cell, it selects all the precedents, and you can even go to the keyboard and press Enter to loop through each of those precedents.
But, to go back, F5 and then click OK, so is there some way to write a macro that will do F5, OK?
So we're going to switch over to VBA here, AlT+F11, and I wrote this tiny little macro, I called it GoBack, and we're using a feature that is much maligned, because it is not reliable.
But here we're just doing two keys, and I've tested a few times, that's called Application.SendKeys, and that in quotes we're sending an F5, that has to be in {}, and then an Enter.
So I'm going to switch back.
Once you've added this, we're going to add a little function to our Quick Access Toolbar.
Right, click Customize Quick Access Toolbar, look under Macros, and the GoBack macro.
You can change that icon of course, but I'll just click OK.
And so we'll double click the cell, select the precedence, and now click the GoBack to go back.
Boy, that was so fast.
On a slower computer you can actually see the Go To dialog box up there.
So a couple of cool tricks that most people don't know about, Ctrl+[ to select all the precedents, Ctrl+ ] to go back.
If you're a mouse person, you have to turn off the "Allow editing directly in cells" that way the double click will allow you to select it.
And then of course, either Ctrl+ ], or if you want to continue to use the mouse, the little GoBack macro.
Alright well hey, I want to thank Mark for sending that question in, I want to thank you for stopping by, we'll you next time for another Netcast from MrExcel!
