Wyatt asks if there is a way to have SmartArt float above a worksheet and always stay in view. Today, in Episode #1366, Bill shows us how to create a short macro to solve the problem, although not perfectly. ...Learn Excel 97-2007 from MrExcel.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1366: Keep SmartArt In One Place.
Okay, all right, boy good question today sent in by Wyatt.
Wyatt has some SmartArt and as he scrolls the worksheet up and down, he wants the SmartArt to always stay in view and I have a solution day and I don’t like my solution.
I'm hoping someone out there has a much better solution.
So, as always, when you have a better solution than me write to bill@mrexcel.com and we'll see what we can do, maybe get you on a future podcast.
So first thing I see here is that the name of this SmartArt when I select the SmartArt is Diagram1.
I do Alt+F11 and we want to go into the sheet.
So now I'm looking at the code for the sheet and from the left drop-down choose worksheet-- that's the only choice and let's get it that way so you can see the right drop down, all right.
Right drop down--I am hoping that there's something in here called After Scroll or something like that but there is not an event handler for scrolling.
So I'm going to say, all right, let's go with SelectionChange.
Already wrote this macro and did a little testing and with ActiveSheet.Shapes(“Diagram 1”),I can control the left in the top.
Now the problem is I'm not sure where I want to keep it all the time, so I use this cool trick; I actually went to the MrExcel message board for a post there by Andrew Paulson.
So ActiveWindow.VisibleRange and that's pretty cool because it shows me how many rows there are and I could ask for dot Rows parenthesis 1 dot Row to find out what the top row in the visible scroller is.
Well that's cool but I actually didn't want just the row I wanted to know a particular row and column.
So here's what I did; I said I always want this SmartArt to appear at the second row, third column and just use the dot left from that cell and the dot top from that cell.
Okay, so now that we have it in let's take a look.
Did you see that?
Anytime when I select a cell it's going to put the diagram back in a certain place.
Also be aware that the boundary box around the SmartArt is huge, so even though it's going to row 2, column 3.
You know, the SmartArt is not exactly where I want it to be.
So this is cool if I'm a person who uses page down.
Right so I do page down and the macro puts it back but if I'm a mouse person (which I’m not), I would scroll down-- I haven't selected a new cell yet so that macro hasn't fired.
It's when I finally click a cell here that the thing appears.
Not sure I like that.
Also, I'm definitely not sure that I like the fact that the SmartArt is going to move when I make a column wider.
Oh it just moved over there, it's still in the same spot row 2 column 3 of the visible row but that changes as I change the column lists.
Not sure I like that.
All right, so is there a solution to this?
Yeah, sort of, maybe; not perfect and I would be interested to see if anyone out there has a better solution.
Just send your macro code to bill@mrexcel.com and we get you on a future podcast Well hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
Learn Excel from MrExcel podcast episode 1366: Keep SmartArt In One Place.
Okay, all right, boy good question today sent in by Wyatt.
Wyatt has some SmartArt and as he scrolls the worksheet up and down, he wants the SmartArt to always stay in view and I have a solution day and I don’t like my solution.
I'm hoping someone out there has a much better solution.
So, as always, when you have a better solution than me write to bill@mrexcel.com and we'll see what we can do, maybe get you on a future podcast.
So first thing I see here is that the name of this SmartArt when I select the SmartArt is Diagram1.
I do Alt+F11 and we want to go into the sheet.
So now I'm looking at the code for the sheet and from the left drop-down choose worksheet-- that's the only choice and let's get it that way so you can see the right drop down, all right.
Right drop down--I am hoping that there's something in here called After Scroll or something like that but there is not an event handler for scrolling.
So I'm going to say, all right, let's go with SelectionChange.
Already wrote this macro and did a little testing and with ActiveSheet.Shapes(“Diagram 1”),I can control the left in the top.
Now the problem is I'm not sure where I want to keep it all the time, so I use this cool trick; I actually went to the MrExcel message board for a post there by Andrew Paulson.
So ActiveWindow.VisibleRange and that's pretty cool because it shows me how many rows there are and I could ask for dot Rows parenthesis 1 dot Row to find out what the top row in the visible scroller is.
Well that's cool but I actually didn't want just the row I wanted to know a particular row and column.
So here's what I did; I said I always want this SmartArt to appear at the second row, third column and just use the dot left from that cell and the dot top from that cell.
Okay, so now that we have it in let's take a look.
Did you see that?
Anytime when I select a cell it's going to put the diagram back in a certain place.
Also be aware that the boundary box around the SmartArt is huge, so even though it's going to row 2, column 3.
You know, the SmartArt is not exactly where I want it to be.
So this is cool if I'm a person who uses page down.
Right so I do page down and the macro puts it back but if I'm a mouse person (which I’m not), I would scroll down-- I haven't selected a new cell yet so that macro hasn't fired.
It's when I finally click a cell here that the thing appears.
Not sure I like that.
Also, I'm definitely not sure that I like the fact that the SmartArt is going to move when I make a column wider.
Oh it just moved over there, it's still in the same spot row 2 column 3 of the visible row but that changes as I change the column lists.
Not sure I like that.
All right, so is there a solution to this?
Yeah, sort of, maybe; not perfect and I would be interested to see if anyone out there has a better solution.
Just send your macro code to bill@mrexcel.com and we get you on a future podcast Well hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
