Deleting a Command Bar 14May

  • Thread starter Thread starter Neal Zimm
  • Start date Start date
N

Neal Zimm

Hi All,
When learning about menus and command bars from Walkenbach's book, I set
up one called "rmws1". I can't find the code that did it, probably forgot to
use Temporary. I have others that delete properly @ the beforeclose event.

I use excel 2002.

I don't want it, and it keeps 'popping' up @ workbook open,
and I delete it with: Application.CommandBars("rmws1").Delete
but it still flashes on the screen first.

Tried opening Excel10.xlb in the Excel folder, but I guess it's
behind the scenes.

How do I permanently delete it ?
Thanks, Neal
 
Neal,

You could try
Sub DelBars()
Dim bar As CommandBar
For Each bar In Application.CommandBars
If Not bar.BuiltIn Then bar.Delete
Next
End Sub
 
Or

Sub DeleteCustomMenuItem()
Dim cbar As CommandBar

For Each cbar In Application.CommandBars
If cbar.Name = "rmws1" Then cbar.Delete
Next

End Sub
 
Well Mangler,
Your code deleted it, but it came back.
Re-read my question and RE-ADDED IT BUT WITH THE TEMPORARY PARM.

Deleted it again. Closed the workbook.

Re-opened the offending workbook, and it now appears to be gone for good.
Thanks.
 
Peter,
From the mso help I didn't think you could "attach" a command bar to a
workbook. These guys are at the office level, no ? In the Help its says
there's no
programmatic was to return the command bars attached to a workbook.

By the way, if one deletes the Excel10.xlb workbook, (that's the templates,
isn't it?)
Does Excel re-create it? I was thinking about that as a last gasp method.

Here's my response to Wrangler re: how it's now gonzo.

Well Mangler,
Your code deleted it, but it came back.
Re-read my own question and RE-ADDED IT BUT WITH THE TEMPORARY PARM.

Deleted it again. Closed the workbook.

Re-opened the offending workbook, and it now appears to be gone for good.
Thanks.
 
Sounds like an attached toolbar to me. In 97-2003 (you have 2002 right?) do
Customize toolbars, Attach... and follow your nose (ensure the suspicious
workbook is active first)

If you delete your xlb yes a new default one will be created, though you'll
lose all your customized toolbars of course.

Regards,
Peter T
 
Peter, Sorry for the delay in getting back to you.
Thanks for the xlb answer.

I have a personality flaw where I get too precise with the English language.

Of course the toolbar is "attached" to the Wbk, workbook VBA code created
it, and another Wbk, without the VBA, will not show that tool bar. My last
question on this
matter is inside the command bars loop below.

My reading of the the Help phrase, "There is no programmatic way....."
meant
that:
If my Wbk name is "D3333.xls",

Dim CBar As CommandBar

For Each CBar in CommandBars

'What can you put here that will provide data about
' the command bars created for the above named workbook and not
'any other Wbk without doing something "special" ?? Special
stuff would
'include putting "D3333.xls" into the .Tag property when the
toolbar or menu
'was created.

Next CBar

' NOTE: VBA that scans the VBE source code modules for a project
might be
able to do it, but I don't think that what the MSoft help phrase
meant.

Thanks again for your time on this,
Neal Z.
 
I am confused both with what you have and your overall objectives. What does
this mean -
Of course the toolbar is "attached" to the Wbk, workbook VBA code created
it,
Click to expand...

Normally a CommandBar does not become "attached" to a workbook unless you
manually attached it. Please refer to the instructions I gave you last time
to determine if the CommandBar is attached. If it is, delete it, thereafter
I recommend don't attach toolbars to a workbook. There's no need and
attached toolbars can cause problems.

If the commandbar is not attached, try and explain your overall objective.
I'm vaguely guessing you have multiple workbooks all attempting to create a
similarly named toolbar.
I have a personality flaw where I get too precise with the English
language.
Click to expand...

Absolutely no problem to be too precise (English doesn't need to be perfect
though), it's when things are not clear it becomes a guessing game for those
trying to assist.

FWIW commandbars do not have a Tag or Parameter property, commandbar
controls do though

Regards,
Peter T
 
Peter,
Thanks so much for your time on this.
The points you raise are spot on and I misspoke about the .Tag property.

I did not mean to convey I have multiple workbooks (wbk) creating the
same command bar, that is not the case.

What I have is an addin I've been building for a couple of years now that
I hope to sell, eventually. I've been in systems for a long long time but I'm
self taught in VBA via Walkenbach's book and the helpful VIP's like you.

I was very imprecise, LOL, when I said the command bar was attached to
the wbk. It is not. At the user's wbk open event, I build a menu for the
tasks the addin code executes. It's deleted @ user wbk deactive or close
events.

In addition, the user's wbk has a worksheet (ws) named "Menu". When the
user activates it, the ws activate event creates a floating command bar. The
Menu ws shows a lot of explanatory text about the same tasks in the "regular"
dropdown (popup) menu @ the top of the screen. (Yeah, I probably should have
developed formal Help functionality, but it was early in the game and this
was a lot easier.) The user community for the addin are NOT financial types,
know 'bare bones' about excel, and my thought was that the Menu ws text would
be helpful so why not run a macro from it?. (No need for you to answer that
question.)

The user can select a task cell on the Menu ws, and then just click the
floater and a macro will execute.

@ Menu ws deactivation the floater command bar is deleted so it does not
appear on other ws's.

The floater that was ALSO showing up, was a stray, an error, that found
it's way to the .xlb wbk as I was learning about command bars and is the
puppy that kicked off this whole thread.

My objective was to permanently delete it so it never appeared again.

Thanks again,
Neal
 
Two things -
I did not mean to convey I have multiple workbooks (wbk) creating the
same command bar, that is not the case. and -
In addition, the user's wbk has a worksheet (ws) named "Menu". When the
user activates it, the ws activate event creates a floating command bar.
Click to expand...

Whether floating or not it's still a commandbar and it sounds like each
workbook creates a very similar if not identical commandbar. But it sounds
like you've got all that managed.
I was very imprecise, LOL, when I said the command bar was attached to
the wbk. It is not. and -
The floater that was ALSO showing up, was a stray, an error, that found
it's way to the .xlb wbk as I was learning about command bars and is the
puppy that kicked off this whole thread.
Click to expand...

"that found it's way back to the .xlb" That suggests the toolbar still
exists in the collection. Maybe it's some how become attached to your xla in
the early days of development. Why not change the xla's IsAddin property to
False and follow the instructions I gave a couple of messages back to ensure
no bar is attached. Don't forget to reset the IsAddin property when done.
Close *all* instances of Excel (keep in mind the xlb will get updated to the
state of bars in the last instance to be closed). Start a new instance and
attempt to delete it

CommandBars("myBar").Delete

Close/restart Excel and check. If it's still there either all instances were
not closed or it is attached to a workbook.
helpful VIP's like you.
Click to expand...

I take it you mean MVP and thanks. Curiosity how did you know, I don't
normally add it to my signature.

Regards,
Peter T
 
Peter,
Good stuff, and thanks, I'll do your steps later today.

Re: VIP, this is a hoot, I actually meant to type MVP, (maybe it's some
kind of Freudian thing) but in truth all you guys are Very Important Person's
to me and the others gaining knowledge here.
So, I didn't know, but I've read a lot of your answers on this board,
and the designation fits you well.

Regards, and thanks again.
Neal Z.
 
Back
Top