Greg is trying to get rid of a Macro Button left behind by a former co-worker. Since there are two kinds of buttons [Form Control and ActiveX buttons] this can be harder than it seems. Episode #1279 shows you all the possibilities.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Episode 1279: Kill the Button.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
Today's question was sent in by Greg.
Greg has a workbook set up by someone who no longer works for the company and there's some macro buttons in the workbook.
Greg you like to either move them or he said really “I just want to get rid of them how can I get rid of it?” And you know the problem is, here is two different buttons.
These buttons.
There's two kinds of buttons in Excel.
There's something called a Form Control and an Active X Control and I didn't see Greg's workbook.
I said the first thing I have to figure out is if you have a Form Control or an Active X Control.
If you have a Form Control, well then it's pretty simple; of course if you left click on it, it runs the macro that's not what we want to do.
So you want to right click.
Right click on the button; now you have a few options here cut, copy and so on or you can just left click them which selects the button.
We can now delete that button, we can move the button, we can resize the button and we can do whatever we need to do with that regular old Form Control button.
So, now again right click and then left-click, just choose the button.
A few people say hey no just Ctrl+Click and just select the button.
That's true that works as well.
I'm just for whatever reason—I right click.
Now this kind of button is an ActiveX button.
These are a little bit tougher because you have to go into something called Design mode.
Of course we click the button, it runs the macro.
Okay, well now ActiveX controls, you the ActiveX toolbar in Excel 2003 or in Excel 2007.
You would go to the old office icon choose Excel options, show Developer tab in the ribbon.
In Excel 2010 I right click the ribbon, customize the ribbon and make sure to choose developer; it's usually unchecked and then you have the Developer tab.
On the Developer tab you come over here and go into design mode.
If you are in Excel 2003 look for this same icon although much smaller, first icon on toolbar there.
Go into design mode and once we're in design mode we can choose the button and press Delete to get rid of it.
All right, so I sent both of those to Greg and he writes back and he says, “It’s not working.” What do you mean it’s not working?
I do it and it doesn't go away.
All right, so check this out I’m going to Ctrl+Click and press Delete but it’s still there.
Ctrl+Click, press Delete.
Still there.
All right, now pause it on your Ipod, go back and replay that.
Now I want you to watch it right up here in the name box and you'll get a clue.
I'm going to Ctrl+Click.
Look at that; this is Button 7.
For whatever reason whoever set up this workbook didn't realize what they were doing and they ended up creating ten or twelve buttons that were the exact same size to the exact same thing right on top of each other.
I almost wonder if they did it with a macro; which is possible.
So when I press Delete here to get rid of Button 7 there's another one but that other one happens to be called Button 6 and so on so.
What Greg had to do was delete, delete, delete, delete, delete, delete until he got rid of all those buttons.
Look, it’s very maddening.
One of those things that you know, what are the odds of that that?
That the guy who set it up accidentally managed to put a whole bunch of buttons there but he did.
All right, so you need to get rid of a button or move a button try and right click it and if that doesn't do it then you know that you have an ActiveX button you need to find Design mode; harder to find because most people don't have the Developer tab in the ribbon and then just watch out for the weird case where someone managed to put several buttons all on top of each other, all the exact same size and when you delete looks like the thing is still there.
Well hey I want to thank Greg for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Episode 1279: Kill the Button.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen.
Today's question was sent in by Greg.
Greg has a workbook set up by someone who no longer works for the company and there's some macro buttons in the workbook.
Greg you like to either move them or he said really “I just want to get rid of them how can I get rid of it?” And you know the problem is, here is two different buttons.
These buttons.
There's two kinds of buttons in Excel.
There's something called a Form Control and an Active X Control and I didn't see Greg's workbook.
I said the first thing I have to figure out is if you have a Form Control or an Active X Control.
If you have a Form Control, well then it's pretty simple; of course if you left click on it, it runs the macro that's not what we want to do.
So you want to right click.
Right click on the button; now you have a few options here cut, copy and so on or you can just left click them which selects the button.
We can now delete that button, we can move the button, we can resize the button and we can do whatever we need to do with that regular old Form Control button.
So, now again right click and then left-click, just choose the button.
A few people say hey no just Ctrl+Click and just select the button.
That's true that works as well.
I'm just for whatever reason—I right click.
Now this kind of button is an ActiveX button.
These are a little bit tougher because you have to go into something called Design mode.
Of course we click the button, it runs the macro.
Okay, well now ActiveX controls, you the ActiveX toolbar in Excel 2003 or in Excel 2007.
You would go to the old office icon choose Excel options, show Developer tab in the ribbon.
In Excel 2010 I right click the ribbon, customize the ribbon and make sure to choose developer; it's usually unchecked and then you have the Developer tab.
On the Developer tab you come over here and go into design mode.
If you are in Excel 2003 look for this same icon although much smaller, first icon on toolbar there.
Go into design mode and once we're in design mode we can choose the button and press Delete to get rid of it.
All right, so I sent both of those to Greg and he writes back and he says, “It’s not working.” What do you mean it’s not working?
I do it and it doesn't go away.
All right, so check this out I’m going to Ctrl+Click and press Delete but it’s still there.
Ctrl+Click, press Delete.
Still there.
All right, now pause it on your Ipod, go back and replay that.
Now I want you to watch it right up here in the name box and you'll get a clue.
I'm going to Ctrl+Click.
Look at that; this is Button 7.
For whatever reason whoever set up this workbook didn't realize what they were doing and they ended up creating ten or twelve buttons that were the exact same size to the exact same thing right on top of each other.
I almost wonder if they did it with a macro; which is possible.
So when I press Delete here to get rid of Button 7 there's another one but that other one happens to be called Button 6 and so on so.
What Greg had to do was delete, delete, delete, delete, delete, delete until he got rid of all those buttons.
Look, it’s very maddening.
One of those things that you know, what are the odds of that that?
That the guy who set it up accidentally managed to put a whole bunch of buttons there but he did.
All right, so you need to get rid of a button or move a button try and right click it and if that doesn't do it then you know that you have an ActiveX button you need to find Design mode; harder to find because most people don't have the Developer tab in the ribbon and then just watch out for the weird case where someone managed to put several buttons all on top of each other, all the exact same size and when you delete looks like the thing is still there.
Well hey I want to thank Greg for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
