Bulk-Delete All Custom Commandbars

[KL]

Hi there,

Soppose I have bunch of files wich have quite a few different custom
commandbars (some are generated by code and some manually attached to the
file). The commandbar names may vary from file to file so it is difficult to
predict them. Is there a way to bulk-delete all CUSTOM commandbars without
having to specify their names?

Something like this:
For each cb in Application.CommandBars.CustomBars
cb.Delete
next cb

Also, is it possible in VBA to tell wheather a custom bar is attached to a
file?

Thanks,
KL

 

[Tom Ogilvy]

from the help example for the Builtin property of a commandbar:

foundFlag = False
deletedBars = 0
For Each bar In CommandBars
If (bar.BuiltIn = False) And (bar.Visible = False) Then
bar.Delete
foundFlag = True
deletedBars = deletedBars + 1
End If
Next
If Not foundFlag Then
MsgBox "No command bars have been deleted."
Else
MsgBox deletedBars & " custom command bar(s) deleted."
End If
I can't say about the ability to inventory attached bars. I am not familiar
with any thing that would support that.

 

[Tom Ogilvy]

Under the commandbars property of the Workbook object, in help it states:

"There is no programmatic way to return the set of command bars attached to
a workbook."

 

[Ben McBen]

Theres a lovely property "builtin" - from the help page:

BuiltIn Property
See Also Applies To Example Specifics
True if the specified command bar or command bar control
is a built-in command bar or control of the container
application. False if it's a custom command bar or
control, or if it's a built-in control whose OnAction
property has been set. Read-only Boolean.

Example
This example deletes all custom command bars that aren't
visible.

foundFlag = False
deletedBars = 0
For Each bar In CommandBars
If (bar.BuiltIn = False) And (bar.Visible = False) Then
bar.Delete
foundFlag = True
deletedBars = deletedBars + 1
End If
Next
If Not foundFlag Then
MsgBox "No command bars have been deleted."
Else
MsgBox deletedBars & " custom command bar(s) deleted."
End If

 

[KL]

Tom and Ben,

Thanks to both of you - that does the trick.

KL