Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk-Delete All Custom Commandbars
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk-Delete All Custom Commandbars
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. -- Regards, Tom Ogilvy "KL" <lapink2000(at)hotmail.com wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk-Delete All Custom Commandbars
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." -- Regards, Tom Ogilvy "KL" <lapink2000(at)hotmail.com wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk-Delete All Custom Commandbars
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk-Delete All Custom Commandbars
Tom and Ben,
Thanks to both of you - that does the trick. KL "Ben McBen" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Cell Naming Bulk Delete | Excel Worksheet Functions | |||
Prob: Custom CommandBars and .xls Files Being Re-Opened | Excel Programming | |||
Custom faces for custom menus/commandbars | Excel Programming | |||
Question on custom commandbars | Excel Programming | |||
Temporarily delete ALL CommandBars? | Excel Programming |