Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding and Deleting custom commandbars
The code I have currently in an addin is below. What I am finding is that
sometimes Excel doesn't close properly or for any other reason the adding of these command bars becomes duplicated. I have had to remove up to twenty on some of my staffs computers. How can I write this so that it checks to see if the command bar exists and if it does to not add it again? Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Import DR Data File").Delete Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Daily Revenue Reset").Delete End Sub Private Sub Workbook_Open() Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Import DR Data File" .FaceId = 312 .BeginGroup = True .OnAction = "MorningReport" End With Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Daily Revenue Reset" .FaceId = 1678 .BeginGroup = False .OnAction = "reset_morning_reports" End With End Sub -- Thanks! Shane W |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding and Deleting custom commandbars
"whylite" wrote in message ... The code I have currently in an addin is below. What I am finding is that sometimes Excel doesn't close properly or for any other reason the adding of these command bars becomes duplicated. I have had to remove up to twenty on some of my staffs computers. How can I write this so that it checks to see if the command bar exists and if it does to not add it again? Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Import DR Data File").Delete Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Daily Revenue Reset").Delete End Sub Private Sub Workbook_Open() Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add Try calling Workbook_BeforeClose(False) as the first line of your Workbook_Open. Rob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding and Deleting custom commandbars
Why not delete it first just in case it exists?
On Error Resume Next Application.CommandBars("mybar").Delete Mike "whylite" wrote: The code I have currently in an addin is below. What I am finding is that sometimes Excel doesn't close properly or for any other reason the adding of these command bars becomes duplicated. I have had to remove up to twenty on some of my staffs computers. How can I write this so that it checks to see if the command bar exists and if it does to not add it again? Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Import DR Data File").Delete Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Daily Revenue Reset").Delete End Sub Private Sub Workbook_Open() Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Import DR Data File" .FaceId = 312 .BeginGroup = True .OnAction = "MorningReport" End With Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Daily Revenue Reset" .FaceId = 1678 .BeginGroup = False .OnAction = "reset_morning_reports" End With End Sub -- Thanks! Shane W |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding and Deleting custom commandbars
One way:
Note that you're adding custom Controls, not custom CommandBars... First, make sure you set the Temporary argument to True in the ..Controls.Add method. Second, you can delete leftover controls before adding their replacements with something like: With Application.CommandBars("Worksheet Menu Bar") On Error Resume Next .FindControl(Tag:="ImportDRControl").Delete .FindControl(Tag:="DailyRevenueReset").Delete On Error GoTo 0 With .Controls With .Add(Type:=msoControlButton, Temporary:=True) .Caption = "Import DR Data File" .FaceId = 312 .BeginGroup = True .OnAction = "MorningReport" .Tag = "ImportDRControl" End With With .Add(Type:=msoControlButton, Temporary:=True) .Caption = "Daily Revenue Reset" .FaceId = 1678 .BeginGroup = False .OnAction = "reset_morning_reports" .Tag = "DailyRevenueReset" End With End With End With Third - if it's possible that the user might have a custom menu bar, you might want to substitute Application.CommandBars.ActiveMenuBar for Application.CommandBars("Worksheet Menu Bar") In article , whylite wrote: The code I have currently in an addin is below. What I am finding is that sometimes Excel doesn't close properly or for any other reason the adding of these command bars becomes duplicated. I have had to remove up to twenty on some of my staffs computers. How can I write this so that it checks to see if the command bar exists and if it does to not add it again? Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Import DR Data File").Delete Application.CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("Daily Revenue Reset").Delete End Sub Private Sub Workbook_Open() Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Import DR Data File" .FaceId = 312 .BeginGroup = True .OnAction = "MorningReport" End With Set newmenuitem = Application.CommandBars _ ("Worksheet Menu Bar").Controls("Tools").Controls.Add With newmenuitem .Caption = "Daily Revenue Reset" .FaceId = 1678 .BeginGroup = False .OnAction = "reset_morning_reports" End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CommandBars & Custom | Excel Programming | |||
Bulk-Delete All Custom Commandbars | Excel Programming | |||
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 |