Ams asks if there is some VBA that will tell you the previously selected cell. Say you are in A1, then click in B1. Is there any VBA that will tell you that the previous cell was A1?
It seems like this should be possible. In today's episode, see how Bill puzzles out the solution by using the macro recorder to learn about Names then VBA AutoComplete to find the right property. Near the end of the video, I add two new names to show that the value of A1 was "X" when you selected it, but "Z" when you left it.
It seems like this should be possible. In today's episode, see how Bill puzzles out the solution by using the macro recorder to learn about Names then VBA AutoComplete to find the right property. Near the end of the video, I add two new names to show that the value of A1 was "X" when you selected it, but "Z" when you left it.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1743.
Address of the Previously Selected Cell?
I have to tell you, I love doing the MrExcel podcast.
Some really interesting questions and get to think about things that you've never really thought about before.
Today's question sent in by Ams A from YouTube.
I was wondering if you can help me I how can I make VBA to track the address of the previous active cell.
For example, my cell was A1 and then I click on B1 I need to get cell address as A1 stored in some and I could never get to the rest of this comment by the way.
So, I don't know what the rest of the question was, but we want to be able to store where we were previously.
So, check this out I have it working like right now, I'm in cell G1 and then I want to go click in another cell and the previous cell is recording where I just was.
All right! Now, where are we going to store this information that's the big question and what I decided to do and I actually did this with the Macro Recorder turned on.
So, I'm going to record a Macro just how to store text in name, store text in name will go to the formulas tab and we define a name we'll just call the name testing and will say that this is equal to in quotes hello, click OK and we'll stop recording then I went and looked at that code.
So, how to store text in a name, Edit, all right and it turns out.
So, they're doing a names.ad and refers to R1C1 is equal to double quote that's, that's the way to put a quote inside of a quote.
So, here's this this whole string here is a quoted string anytime inside of here that you want to put a single quote you have to put two quotes.
All right and they're also doing something with a comment and none of this really felt right to me because I don't want to add the name i just want to be able to store the value in the name, but this gave me enough information that we're dealing with you know something called names and so, I kind of came down here and did some tests more and active workbook.names testing.
All right and right here once I had this dot they're going to give me a list of all of the properties of that name and sure enough there's something called refers to, refers to local would be in the local language reverse to R1C1 that's not what we want here we want to just do refers to and so, let's do debug.print.
All right! so, this is cool.
This is, you know I've been doing VBA, oh my gosh almost 20 years, but I don't work with names enough to know you know what I needed, but by doing activeworkbook.names the parentheses and then the dot they the intelliSense or autocomplete let's call it autocomplete gives me the list of possible entries.
So, we'll run that Macro here and we'll see what we get and from you know that little test I think.
Hey, there we go.
So, inside the refers to property they're storing hello that means that if I have a name defined, I can use this refers to to hold things and to get them back later.
All right! So, the next steps here I came out and I defined a name just like I did for testing, but I created names called last cell and prior cell and I initially just set them to hello like I did with testing.
So, you have to set up last cell, prior cell.
Actually I ended up using curr cell for current cell and prior cell.
So, those are the two you really need you don't need this one, you don't need last cell and you don't need our testing that I did.
So, set up to named ranges using formulas to find name just set them to hello.
All right, then we're going to come to VBA.
So, that's Alt+F11 in VBA you're gonna look at the Project Explorer in the Project Explorer if you don't see that, go to View, Project Explorer or Control+R look for the sheet double-click the sheet this will initially start out blank it needs to go up here and select worksheet from the top left drop down, the right drop down immediately changes to selection change.
All right! So, now we're going to have two names one called curr cell, one called prior cell, the second line of code is going to say the current cell is equal to the target.address that's the cell that we just selected the current cell and we're going to wrap that in quotes.
All right! So, this is kind of wild here we start out with an equal sign.
and then a single quote is represented by two quotes close that initial string.
So, right there we have an equal and a quote, ampersand target.address and then these four quotes here is a way of putting a quote inside of another quote.
That's really confusing four quotes will get you one quote and so, that will store the the address of the current cell in this name and before we do that we're going to say the prior cell is equal to the current cell.
So, before we record the new current cell we're going to take that value that was stored in the current cell and move it over to the prior cell and now, it all just works these formulas here are really simple equal prior cell, even kind of shows up there in the in the list press tab equal curr cell tab and it all works.
Now, I started to think about this actually wrote this macro last night and as I started to record it.
It's like why, why does he want this information why does he want to know the last cell he was in.
Maybe he's trying to figure out, if something just changed in that cell.
So, I'm going to control+tab here to another workbook called podcast 1743a where I created two more variables Cval and Pval.
So, it shows the current cell is J2 the previous cell is J3 and what i think is cool is this is the value when I initially selected J3 and this is the value now.
Value now is the indirect, but these are those two new variables that I setups Cval and Pval and so, let's come here and select MNO and we'll put in XYZ press enter.
All right! So, now the current cell is J3, but the previous l was J2.
It was a MNO when I selected it and now, it's XYZ.
Isn't that a cool little bit of functionality using names not to store cells or anything like that just to store an actual value some text and that text happens to be the address.
The code to make the previous value and current value work very similar I just copied those two lines down Pval, Cval the only big change here is before we are storing target.address.
I'm now storing target.value interesting note these top two lines of code would work if I selected a range these will only work when I select single values.
So, a little bit more work would have to be done if you really wanted to get just, the first cell of the selected range.
Oh! Hey, all right I wanna thank for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode 1743.
Address of the Previously Selected Cell?
I have to tell you, I love doing the MrExcel podcast.
Some really interesting questions and get to think about things that you've never really thought about before.
Today's question sent in by Ams A from YouTube.
I was wondering if you can help me I how can I make VBA to track the address of the previous active cell.
For example, my cell was A1 and then I click on B1 I need to get cell address as A1 stored in some and I could never get to the rest of this comment by the way.
So, I don't know what the rest of the question was, but we want to be able to store where we were previously.
So, check this out I have it working like right now, I'm in cell G1 and then I want to go click in another cell and the previous cell is recording where I just was.
All right! Now, where are we going to store this information that's the big question and what I decided to do and I actually did this with the Macro Recorder turned on.
So, I'm going to record a Macro just how to store text in name, store text in name will go to the formulas tab and we define a name we'll just call the name testing and will say that this is equal to in quotes hello, click OK and we'll stop recording then I went and looked at that code.
So, how to store text in a name, Edit, all right and it turns out.
So, they're doing a names.ad and refers to R1C1 is equal to double quote that's, that's the way to put a quote inside of a quote.
So, here's this this whole string here is a quoted string anytime inside of here that you want to put a single quote you have to put two quotes.
All right and they're also doing something with a comment and none of this really felt right to me because I don't want to add the name i just want to be able to store the value in the name, but this gave me enough information that we're dealing with you know something called names and so, I kind of came down here and did some tests more and active workbook.names testing.
All right and right here once I had this dot they're going to give me a list of all of the properties of that name and sure enough there's something called refers to, refers to local would be in the local language reverse to R1C1 that's not what we want here we want to just do refers to and so, let's do debug.print.
All right! so, this is cool.
This is, you know I've been doing VBA, oh my gosh almost 20 years, but I don't work with names enough to know you know what I needed, but by doing activeworkbook.names the parentheses and then the dot they the intelliSense or autocomplete let's call it autocomplete gives me the list of possible entries.
So, we'll run that Macro here and we'll see what we get and from you know that little test I think.
Hey, there we go.
So, inside the refers to property they're storing hello that means that if I have a name defined, I can use this refers to to hold things and to get them back later.
All right! So, the next steps here I came out and I defined a name just like I did for testing, but I created names called last cell and prior cell and I initially just set them to hello like I did with testing.
So, you have to set up last cell, prior cell.
Actually I ended up using curr cell for current cell and prior cell.
So, those are the two you really need you don't need this one, you don't need last cell and you don't need our testing that I did.
So, set up to named ranges using formulas to find name just set them to hello.
All right, then we're going to come to VBA.
So, that's Alt+F11 in VBA you're gonna look at the Project Explorer in the Project Explorer if you don't see that, go to View, Project Explorer or Control+R look for the sheet double-click the sheet this will initially start out blank it needs to go up here and select worksheet from the top left drop down, the right drop down immediately changes to selection change.
All right! So, now we're going to have two names one called curr cell, one called prior cell, the second line of code is going to say the current cell is equal to the target.address that's the cell that we just selected the current cell and we're going to wrap that in quotes.
All right! So, this is kind of wild here we start out with an equal sign.
and then a single quote is represented by two quotes close that initial string.
So, right there we have an equal and a quote, ampersand target.address and then these four quotes here is a way of putting a quote inside of another quote.
That's really confusing four quotes will get you one quote and so, that will store the the address of the current cell in this name and before we do that we're going to say the prior cell is equal to the current cell.
So, before we record the new current cell we're going to take that value that was stored in the current cell and move it over to the prior cell and now, it all just works these formulas here are really simple equal prior cell, even kind of shows up there in the in the list press tab equal curr cell tab and it all works.
Now, I started to think about this actually wrote this macro last night and as I started to record it.
It's like why, why does he want this information why does he want to know the last cell he was in.
Maybe he's trying to figure out, if something just changed in that cell.
So, I'm going to control+tab here to another workbook called podcast 1743a where I created two more variables Cval and Pval.
So, it shows the current cell is J2 the previous cell is J3 and what i think is cool is this is the value when I initially selected J3 and this is the value now.
Value now is the indirect, but these are those two new variables that I setups Cval and Pval and so, let's come here and select MNO and we'll put in XYZ press enter.
All right! So, now the current cell is J3, but the previous l was J2.
It was a MNO when I selected it and now, it's XYZ.
Isn't that a cool little bit of functionality using names not to store cells or anything like that just to store an actual value some text and that text happens to be the address.
The code to make the previous value and current value work very similar I just copied those two lines down Pval, Cval the only big change here is before we are storing target.address.
I'm now storing target.value interesting note these top two lines of code would work if I selected a range these will only work when I select single values.
So, a little bit more work would have to be done if you really wanted to get just, the first cell of the selected range.
Oh! Hey, all right I wanna thank for stopping by.
We'll see you next time for another netcast from MrExcel.
