Episode 2493
Why is the drop-down arrow not showing up when I create the Excel validation with VBA? It appears after I click Save. But I don't want to save since these numbers are a temporary scenario and I plan on closing without saving.
Also: Using the Selection Pane in Excel to see all shape names
How Excel names shapes.
Why Excel renames Shapes during workbook copy
How to use VBA to detect which shape was selected.
Table of Contents
(0:00) Bug with Shapes in VBA
(0:31) Excel Validation drop-down arrow missing
(3:05) VBA code to delete shapes
(4:07) How Excel Names Shapes
(4:44) Selection Pane in Excel
(5:47) First Validation arrow is named
(7:01) Shapes names restart with new sheet
(7:48) Shapes renamed after Sheet Copy
(9:26) VBA properties for Shapes
(9:50) VBA code to detect which shape clicked
(11:37) Book for learning Excel VBA
(12:02) Thanks for watching
Why is the drop-down arrow not showing up when I create the Excel validation with VBA? It appears after I click Save. But I don't want to save since these numbers are a temporary scenario and I plan on closing without saving.
Also: Using the Selection Pane in Excel to see all shape names
How Excel names shapes.
Why Excel renames Shapes during workbook copy
How to use VBA to detect which shape was selected.
Table of Contents
(0:00) Bug with Shapes in VBA
(0:31) Excel Validation drop-down arrow missing
(3:05) VBA code to delete shapes
(4:07) How Excel Names Shapes
(4:44) Selection Pane in Excel
(5:47) First Validation arrow is named
(7:01) Shapes names restart with new sheet
(7:48) Shapes renamed after Sheet Copy
(9:26) VBA properties for Shapes
(9:50) VBA code to detect which shape clicked
(11:37) Book for learning Excel VBA
(12:02) Thanks for watching
Transcript of the video:
Learn Excel from MrExcel Podcast, episode 2493.
Now, this is one that can have a lot of names.
Overcoming Bugs with Excel Shapes in VBA.
Why Aren't My Validation drop-down arrows Showing?
How Can I Delete All The Shapes Except For The Validation drop-down arrows?
Why is rectangle 14 Changing Its Name?
VBA to Figure Out Which Shape Was Clicked.
Let's go with this.
More Than You Ever Wanted To Know About Shapes and VBA.
All right, today's question, sent in by Mike.
He's creating a data validation cells with VBA. And he says, "It is so frustrating that the in-cell drop-down does not display the arrow until he clicks save".
But that's not an option for him because Mike is creating several different scenarios, and plans on closing the file without saving to go back to the base scenario.
It's a super annoying bug.
We're talking about this little arrow right here that should show up when we have data validation.
So I tried it here.
First off, let's just create some data validation Alt+D L.
And we're going to choose from a list.
The list is here.
Click okay.
And we get the Drop-down arrow, right?
It works perfectly.
Mike sent me his code, right.
It wasn't exactly this code, but a code similar to this.
And we'll run that quick code.
Ctrl+Shift+V.
And you see that we get the validation, including the little input message.
And if we type something else, the Error that you've chosen an invalid item., right.
So it's working.
It's great.
But for some reason in Mike's code he is not getting the arrow.
It actually looks like this, Ctrl+Shift+X., right.
Ah, so the validation is there., right.
But I'm getting no arrow.
And it's not just that the arrow is not appearing here.
The arrow's also not appearing here or here., right.
So something is happening that is causing this problem.
All right, now, I'm going to take this workbook and save.
When I click save, all right, then the arrows come back.
I was like, "Oh no, I have no idea".
So I went out to my Excel MVP friends.
And again, Brad Yundt, who is the master at solving all VBA problems that I ever encounter, said, "Is there any chance they're running code to delete all shapes?" I'm like, "What?
Why would he be doing that?" But I went back to Mike, and sure enough, Mike, in a very long macro, has another section where he's deleting shapes on the sheet.
And it turns out that deleting all shapes causes this problem.
Now, just as a historical note, I remember long ago, right when I started MrExcel, it was probably back in 2000 or 2001, I ran into someone who had a similar problem.
But their situation was the drop-down arrow was missing.
And when we went to where it should have been, Excel crashed., right.
So kudos to the Excel team for in the last 21 years of handling this much better.
And at the save, bringing the arrows back., right.
Fixed that really nasty, nasty bug.
Now the code that I would use to get rid of all shapes in the sheet is for each shape in ActiveSheet Dot Shapes, Shape Dot Delete, Next Shape.
And so Mike's question: is there some way that we could detect that this is a drop-down arrow and not delete the shapes that are drop-downs?, right.
And my horrible code was to look at Shape Dot Name, which is going to have a different name for each drop-down.
We'll talk about that in a minute.
Look at the left nine characters, see if it's drop-down.
And then if it is, do a Shape Dot Delete.
Now, shout out to Mike Broyles for improving on that code.
He discovered, rather than using Shape Dot Name, that there's a Shape Dot Type.
And Shape Dot Type is one for these regular shapes that we just drew, and the type is nine for the drop-down arrow.
So Mike just changed the code and said, "If Shape Dot Type equals one, then delete it.
Otherwise, if it's a nine, then it doesn't get deleted and we won't screw up the drop-down arrows".
Great bit of code there.
Nice improvement.
Thanks to Mike for coming up with that.
All right, let's talk about how shapes are named.
And this is actually a great mystery.
I'm going to be super curious if anyone can figure out where this particular feature is stored.
Here on the insert tab, under shapes, I've right clicked and added that to the Quick Access Toolbar.
That way I can very quickly add shapes.
And we'll just take a rectangle here, add a rectangle to the worksheet.
And you see that Excel has given that a name of rectangle one.
They knew that there was not another shape on this sheet, no other rectangles on this sheet, and so they called it rectangle one.
Now, to make life a little bit easier here, I'm going to do a cool thing.
Home, find and select in the selection pane.
This is great because it'll show us all of the shapes on the sheet.
And then I'm just going to come here.
I'm going to add a second shape.
I'm going to add a second rectangle.
And very smartly, Excel knows that this is rectangle two., right.
Wow.
That's cool.
And then I'm going to come out here and I'm going to add another shape.
Maybe a triangle.
All right?
Add that.
Oh, and they call it isosceles triangle.
So somewhere there's a counter that's keeping track of how many shapes I've added, and it's adding it to the next item.
Now, we just discovered that data validation is creating a shape.
So let's allow a list here.
We'll just type in here that we are going to allow one, two, three, four.
Click okay.
And you see that we do get the validation arrow.
And over here we have rectangle one, rectangle two, isosceles triangle three, and then drop-down one.
And then we'll do another arrow, data validation.
Allow a list of five, six, seven, eight.
And that then, very annoyingly, gets no name in the list., right.
What's up with that?
Okay.
So we have shape one, two, three, drop-down one, and then we will add another shape.
This is going to be called chord four.
All right, so somewhere Microsoft is keeping track of these shape names., right.
So I'm going to take chord four.
I'm going to control drag it, get chord five, chord six, chord seven, chord eight, chord nine, chord 10, chord 11.
All right, what would happen if I deleted one of the earlier ones?
Like let's say if I take isosceles triangle three and delete it.
Bang, it's gone.
And then make a copy of chord 11.
Will it be smart enough to reuse three?
No.
Right.
It doesn't.
Even if I would delete 12 and 11 and 10, and then insert a new shape, parallelogram 13.
So it's like it's remembering the highest shape number that it's ever used, and uses the next one.
And that property seems to be unique to each sheet.
So here I have rectangle one.
If I would make a copy of it, rectangle two, rectangle three, rectangle four, rectangle five.
Delete rectangle three, and then we get rectangle six., right.
So for each of these sheets, somehow it knows that the next sheet name or shape name is going to be 14 in this case, 15 in that case, 16 in that case.
Where are they storing this?
I went over to VBA, and here on the shape naming worksheet there's no property that I can get to in VBA that will tell me what the next shape name is going to be.
So somewhere inside of Excel they're storing this property.
It's pretty wild.
If anyone can figure out where they're storing this, yeah, that would be great.
I mean, it can't be in the registry.
It has to travel with a workbook.
It's got to be in there somewhere.
Now here's another bug.
This one has frustrated me ever since Excel 2007.
So right now we have a series of shapes.
One, two, four, five, six, seven, eight, nine.
Let's take nine, get rid of nine.
And then 13, 14, 15.
Let's get rid of 15.
All right, so I have a series of shapes.
And now I'm going to take this worksheet and make a copy of it.
The fast way to do that is to hold down the control key while I drag the sheet to the right.
And I get a brand new worksheet.
I used to do this in VBA all the time.
I would have my macro workbook with a series of buttons, and I'd want those buttons to move to the client's workbook.
So I would copy that over.
And I knew that a particular button was rectangle 14, or something like that.
But check this out.
When I do the move or copy, the Excel team has very nicely decided to rename all of the freaking shapes.
So what used to be parallelogram 14 is now parallelogram 10.
I instantly detected this when it happened in Excel 2007.
And I went to the guy on the Excel team who did it.
And I'm like, "What are you doing?" He's like, "I didn't think that would cause a problem.
I'm making it neater for you".
I'm like, "Oh, that's super annoying".
Right?
So now the last question here is, in that particular case, I was counting on a particular shape name being clicked.
And I had code to detect that., right.
But now I can't count on it being called rectangle 14 or parallelogram 14, because we don't know how the shape names are going to be renamed when it goes to a new workbook.
There's a lot of information available on VBA about a particular shape.
We can get the Shape Dot Name, the Shape Dot Top Left Cell Dot Address, the location, the Shape Dot Left, or the Shape Dot Top, or even what's in the text frame.
Text Frame Two Dot Text Range Dot Text will give us the characters that are in that cell., right.
But the question is: how can we know which item was just clicked?
All right, and thanks to Smozgur at MrExcel, who gave us this great code, two little macros here.
The first one is called initShapeEvents, Initialized Shape Events.
And it goes through each shape on the sheet, and it assigns the macro to something called Shape Underscore Click.
So we'll run that.
All right, so now each of these little shapes has a macro assignment.
So the same bit of macro code.
And when I click, this Shape Dot Click is going to run.
And we're going to use Active Sheet Dot Shapes and then Application Dot Caller to figure out which shape was clicked in order to run this macro.
I've used this method a variety of times.
Once we had a whole series of check boxes, 150 check boxes.
And when a checkbox was checked it would put a one in the adjacent cell or something like that.
So this is a method that occasionally you just have a whole array of shapes.
It would be nice if we could use a class module for this, but unfortunately shapes don't work with class modules.
And then once I know the shape I can ask for the Shape Dot Name, Shape Dot Top Left Cell Address, Shape Left Top, and get the text.
Okay.
So we'll use one of these.
I'll click on the N here., right.
And it tells me that that's decagon 17.
The top left corner cell is F11.
This is the X location, the Y location, and the text in the shape is N.
So that gives me five different ways.
Well, actually four different ways.
You'd have to know the X and Y to try and figure out which shape was selected.
And then, if I click on another shape, that information is updated here.
So one single macro able to deal with all 35 different shapes.
Cool little bit of code there.
Hey, I just got a box in the mail this week from Microsoft Press, our new book, Microsoft Excel VBA and Macros for Office 2021 and Microsoft 365.
It's a book that Tracy and I wrote many years ago, back in 2004.
I think this is probably the seventh edition now.
So for great examples on VBA, check out that book.
There'll be an i on the top right hand corner there where you can click.
Well, I want to thank Mike for sending that question in, and for improving on my code.
Thanks to Brad for knowing that the data Validation drop-down arrow disappearing is because of deleting shapes, and to Suat Ozgur for the code to run the same macro no matter which shape is selected.
And I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Now, this is one that can have a lot of names.
Overcoming Bugs with Excel Shapes in VBA.
Why Aren't My Validation drop-down arrows Showing?
How Can I Delete All The Shapes Except For The Validation drop-down arrows?
Why is rectangle 14 Changing Its Name?
VBA to Figure Out Which Shape Was Clicked.
Let's go with this.
More Than You Ever Wanted To Know About Shapes and VBA.
All right, today's question, sent in by Mike.
He's creating a data validation cells with VBA. And he says, "It is so frustrating that the in-cell drop-down does not display the arrow until he clicks save".
But that's not an option for him because Mike is creating several different scenarios, and plans on closing the file without saving to go back to the base scenario.
It's a super annoying bug.
We're talking about this little arrow right here that should show up when we have data validation.
So I tried it here.
First off, let's just create some data validation Alt+D L.
And we're going to choose from a list.
The list is here.
Click okay.
And we get the Drop-down arrow, right?
It works perfectly.
Mike sent me his code, right.
It wasn't exactly this code, but a code similar to this.
And we'll run that quick code.
Ctrl+Shift+V.
And you see that we get the validation, including the little input message.
And if we type something else, the Error that you've chosen an invalid item., right.
So it's working.
It's great.
But for some reason in Mike's code he is not getting the arrow.
It actually looks like this, Ctrl+Shift+X., right.
Ah, so the validation is there., right.
But I'm getting no arrow.
And it's not just that the arrow is not appearing here.
The arrow's also not appearing here or here., right.
So something is happening that is causing this problem.
All right, now, I'm going to take this workbook and save.
When I click save, all right, then the arrows come back.
I was like, "Oh no, I have no idea".
So I went out to my Excel MVP friends.
And again, Brad Yundt, who is the master at solving all VBA problems that I ever encounter, said, "Is there any chance they're running code to delete all shapes?" I'm like, "What?
Why would he be doing that?" But I went back to Mike, and sure enough, Mike, in a very long macro, has another section where he's deleting shapes on the sheet.
And it turns out that deleting all shapes causes this problem.
Now, just as a historical note, I remember long ago, right when I started MrExcel, it was probably back in 2000 or 2001, I ran into someone who had a similar problem.
But their situation was the drop-down arrow was missing.
And when we went to where it should have been, Excel crashed., right.
So kudos to the Excel team for in the last 21 years of handling this much better.
And at the save, bringing the arrows back., right.
Fixed that really nasty, nasty bug.
Now the code that I would use to get rid of all shapes in the sheet is for each shape in ActiveSheet Dot Shapes, Shape Dot Delete, Next Shape.
And so Mike's question: is there some way that we could detect that this is a drop-down arrow and not delete the shapes that are drop-downs?, right.
And my horrible code was to look at Shape Dot Name, which is going to have a different name for each drop-down.
We'll talk about that in a minute.
Look at the left nine characters, see if it's drop-down.
And then if it is, do a Shape Dot Delete.
Now, shout out to Mike Broyles for improving on that code.
He discovered, rather than using Shape Dot Name, that there's a Shape Dot Type.
And Shape Dot Type is one for these regular shapes that we just drew, and the type is nine for the drop-down arrow.
So Mike just changed the code and said, "If Shape Dot Type equals one, then delete it.
Otherwise, if it's a nine, then it doesn't get deleted and we won't screw up the drop-down arrows".
Great bit of code there.
Nice improvement.
Thanks to Mike for coming up with that.
All right, let's talk about how shapes are named.
And this is actually a great mystery.
I'm going to be super curious if anyone can figure out where this particular feature is stored.
Here on the insert tab, under shapes, I've right clicked and added that to the Quick Access Toolbar.
That way I can very quickly add shapes.
And we'll just take a rectangle here, add a rectangle to the worksheet.
And you see that Excel has given that a name of rectangle one.
They knew that there was not another shape on this sheet, no other rectangles on this sheet, and so they called it rectangle one.
Now, to make life a little bit easier here, I'm going to do a cool thing.
Home, find and select in the selection pane.
This is great because it'll show us all of the shapes on the sheet.
And then I'm just going to come here.
I'm going to add a second shape.
I'm going to add a second rectangle.
And very smartly, Excel knows that this is rectangle two., right.
Wow.
That's cool.
And then I'm going to come out here and I'm going to add another shape.
Maybe a triangle.
All right?
Add that.
Oh, and they call it isosceles triangle.
So somewhere there's a counter that's keeping track of how many shapes I've added, and it's adding it to the next item.
Now, we just discovered that data validation is creating a shape.
So let's allow a list here.
We'll just type in here that we are going to allow one, two, three, four.
Click okay.
And you see that we do get the validation arrow.
And over here we have rectangle one, rectangle two, isosceles triangle three, and then drop-down one.
And then we'll do another arrow, data validation.
Allow a list of five, six, seven, eight.
And that then, very annoyingly, gets no name in the list., right.
What's up with that?
Okay.
So we have shape one, two, three, drop-down one, and then we will add another shape.
This is going to be called chord four.
All right, so somewhere Microsoft is keeping track of these shape names., right.
So I'm going to take chord four.
I'm going to control drag it, get chord five, chord six, chord seven, chord eight, chord nine, chord 10, chord 11.
All right, what would happen if I deleted one of the earlier ones?
Like let's say if I take isosceles triangle three and delete it.
Bang, it's gone.
And then make a copy of chord 11.
Will it be smart enough to reuse three?
No.
Right.
It doesn't.
Even if I would delete 12 and 11 and 10, and then insert a new shape, parallelogram 13.
So it's like it's remembering the highest shape number that it's ever used, and uses the next one.
And that property seems to be unique to each sheet.
So here I have rectangle one.
If I would make a copy of it, rectangle two, rectangle three, rectangle four, rectangle five.
Delete rectangle three, and then we get rectangle six., right.
So for each of these sheets, somehow it knows that the next sheet name or shape name is going to be 14 in this case, 15 in that case, 16 in that case.
Where are they storing this?
I went over to VBA, and here on the shape naming worksheet there's no property that I can get to in VBA that will tell me what the next shape name is going to be.
So somewhere inside of Excel they're storing this property.
It's pretty wild.
If anyone can figure out where they're storing this, yeah, that would be great.
I mean, it can't be in the registry.
It has to travel with a workbook.
It's got to be in there somewhere.
Now here's another bug.
This one has frustrated me ever since Excel 2007.
So right now we have a series of shapes.
One, two, four, five, six, seven, eight, nine.
Let's take nine, get rid of nine.
And then 13, 14, 15.
Let's get rid of 15.
All right, so I have a series of shapes.
And now I'm going to take this worksheet and make a copy of it.
The fast way to do that is to hold down the control key while I drag the sheet to the right.
And I get a brand new worksheet.
I used to do this in VBA all the time.
I would have my macro workbook with a series of buttons, and I'd want those buttons to move to the client's workbook.
So I would copy that over.
And I knew that a particular button was rectangle 14, or something like that.
But check this out.
When I do the move or copy, the Excel team has very nicely decided to rename all of the freaking shapes.
So what used to be parallelogram 14 is now parallelogram 10.
I instantly detected this when it happened in Excel 2007.
And I went to the guy on the Excel team who did it.
And I'm like, "What are you doing?" He's like, "I didn't think that would cause a problem.
I'm making it neater for you".
I'm like, "Oh, that's super annoying".
Right?
So now the last question here is, in that particular case, I was counting on a particular shape name being clicked.
And I had code to detect that., right.
But now I can't count on it being called rectangle 14 or parallelogram 14, because we don't know how the shape names are going to be renamed when it goes to a new workbook.
There's a lot of information available on VBA about a particular shape.
We can get the Shape Dot Name, the Shape Dot Top Left Cell Dot Address, the location, the Shape Dot Left, or the Shape Dot Top, or even what's in the text frame.
Text Frame Two Dot Text Range Dot Text will give us the characters that are in that cell., right.
But the question is: how can we know which item was just clicked?
All right, and thanks to Smozgur at MrExcel, who gave us this great code, two little macros here.
The first one is called initShapeEvents, Initialized Shape Events.
And it goes through each shape on the sheet, and it assigns the macro to something called Shape Underscore Click.
So we'll run that.
All right, so now each of these little shapes has a macro assignment.
So the same bit of macro code.
And when I click, this Shape Dot Click is going to run.
And we're going to use Active Sheet Dot Shapes and then Application Dot Caller to figure out which shape was clicked in order to run this macro.
I've used this method a variety of times.
Once we had a whole series of check boxes, 150 check boxes.
And when a checkbox was checked it would put a one in the adjacent cell or something like that.
So this is a method that occasionally you just have a whole array of shapes.
It would be nice if we could use a class module for this, but unfortunately shapes don't work with class modules.
And then once I know the shape I can ask for the Shape Dot Name, Shape Dot Top Left Cell Address, Shape Left Top, and get the text.
Okay.
So we'll use one of these.
I'll click on the N here., right.
And it tells me that that's decagon 17.
The top left corner cell is F11.
This is the X location, the Y location, and the text in the shape is N.
So that gives me five different ways.
Well, actually four different ways.
You'd have to know the X and Y to try and figure out which shape was selected.
And then, if I click on another shape, that information is updated here.
So one single macro able to deal with all 35 different shapes.
Cool little bit of code there.
Hey, I just got a box in the mail this week from Microsoft Press, our new book, Microsoft Excel VBA and Macros for Office 2021 and Microsoft 365.
It's a book that Tracy and I wrote many years ago, back in 2004.
I think this is probably the seventh edition now.
So for great examples on VBA, check out that book.
There'll be an i on the top right hand corner there where you can click.
Well, I want to thank Mike for sending that question in, and for improving on my code.
Thanks to Brad for knowing that the data Validation drop-down arrow disappearing is because of deleting shapes, and to Suat Ozgur for the code to run the same macro no matter which shape is selected.
And I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
