Automation Error when deleting command bar button

F

Frank Kabel

Hi all
I've the following problem deleting a commandbutton in the
toolbar:
1. A customized commandbar button is created to invoke a
userform
2. Within the userform the user has the option to change
the commandbarbutton
3. To replace the button the procedure first tries to
delete the button and afterwards to add a new button (with
the new layout). For deleting the following statement is
used:
Application.CommandBars("Standard").Controls
("Button_Title").Delete

This returns an automation error if invoked from the
userform. No problem if invoked directly (e.g. from the
immediate window).
Problem seems to be that the commandbarbutton is
still 'pressed down' due to invoking the userform in the
first place and can therefore not be deleted.

Does anybody has an idea how to solve thisand how to
delete the button (even resetting the button does not
help. Only resetting the complete commandbar would do but
this also remove all other customised settings of this bar)

Frank
 
N

Norman Jones

Hi Frank,

In the absence of a more constructive response,
Only resetting the complete commandbar would do but
this also remove all other customised settings of this bar)
Click to expand...

Why not reset and rebuild the commandbar?
 
F

Frank Kabel

Hi Norman
thanks for the response. The problem with this approach is
as follows:
- the button is inerted in the standard command bar
- the user may have other add-ins, etc. installed that
have changed the commandbar already
- so if I reset the commandbar all other 'customised' icos
would also be lost

Frank
 
N

Norman Jones

Hi Frank,

Does the usedform need to be modal?

If ShowModal is set to false, the CommandButton can be deleted.
 
B

Bob Flanagan

Frank, after all the changes are made except for the button deletion, use an
Ontime command to run a macro that deletes the button.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
T

Tom Ogilvy

Have the macro that now shows the userform, instead show the userform using
a macro called with ontime.

Application.Ontime Now,"Macrotoshowform"

instead. This will allow the button's onaction code to complete and you can
delete the button with the userform.
 
Top