Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not work
Can someone please help me? The following code is not working. I would like
to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not work
Your code should work fine assuming a commandbar with that name exists.
Unless certain, normally best to use On Error resume next ' delete code On Error goto 0 Maybe you have the commandbar "attached" to a workbook which is causing confusion. Regards, Peter T "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Hi Peter,
Thanks for the note. Here is how the command bar is loaded. Is this "attached" as you mention below? If this is not the proper approach, can you please tell me how to resolve? Thanks, Tom Sub Auto_Open() Dim cb As CommandBar Dim cbb As CommandBarButton Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, False, True) cb.Enabled = True cb.Visible = True Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Refresh Data" cbb.FaceId = 159 cbb.OnAction = "InitializeDataInput2" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Generate Reports" cbb.FaceId = 433 cbb.OnAction = "ShowCommandPopupGenerateReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Print Reports" cbb.FaceId = 4 cbb.OnAction = "ShowCommandPopupPrintReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "eMail Reports" cbb.FaceId = 258 cbb.OnAction = "CreateAndEmailReports" End Sub "Peter T" wrote: Your code should work fine assuming a commandbar with that name exists. Unless certain, normally best to use On Error resume next ' delete code On Error goto 0 Maybe you have the commandbar "attached" to a workbook which is causing confusion. Regards, Peter T "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
At a glance your code looks fine, nothing there that "attaches" it to your
workbook. Though it's always worth "attempting" to delete the bar first, under on error resume next, just in case it wasn't deleted last time. A commandbar might have been attached previously, with the workbook active look at Customize toolbars, Attach... or maybe If Not ThisWorkbook.CommandBars Is Nothing Then MsgBox ThisWorkbook.CommandBars(1).Name End If Regards, Peter T "Tom Joseph" wrote in message ... Hi Peter, Thanks for the note. Here is how the command bar is loaded. Is this "attached" as you mention below? If this is not the proper approach, can you please tell me how to resolve? Thanks, Tom Sub Auto_Open() Dim cb As CommandBar Dim cbb As CommandBarButton Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, False, True) cb.Enabled = True cb.Visible = True Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Refresh Data" cbb.FaceId = 159 cbb.OnAction = "InitializeDataInput2" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Generate Reports" cbb.FaceId = 433 cbb.OnAction = "ShowCommandPopupGenerateReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Print Reports" cbb.FaceId = 4 cbb.OnAction = "ShowCommandPopupPrintReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "eMail Reports" cbb.FaceId = 258 cbb.OnAction = "CreateAndEmailReports" End Sub "Peter T" wrote: Your code should work fine assuming a commandbar with that name exists. Unless certain, normally best to use On Error resume next ' delete code On Error goto 0 Maybe you have the commandbar "attached" to a workbook which is causing confusion. Regards, Peter T "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Thanks again. I will give it a try.
"Peter T" wrote: At a glance your code looks fine, nothing there that "attaches" it to your workbook. Though it's always worth "attempting" to delete the bar first, under on error resume next, just in case it wasn't deleted last time. A commandbar might have been attached previously, with the workbook active look at Customize toolbars, Attach... or maybe If Not ThisWorkbook.CommandBars Is Nothing Then MsgBox ThisWorkbook.CommandBars(1).Name End If Regards, Peter T "Tom Joseph" wrote in message ... Hi Peter, Thanks for the note. Here is how the command bar is loaded. Is this "attached" as you mention below? If this is not the proper approach, can you please tell me how to resolve? Thanks, Tom Sub Auto_Open() Dim cb As CommandBar Dim cbb As CommandBarButton Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, False, True) cb.Enabled = True cb.Visible = True Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Refresh Data" cbb.FaceId = 159 cbb.OnAction = "InitializeDataInput2" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Generate Reports" cbb.FaceId = 433 cbb.OnAction = "ShowCommandPopupGenerateReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "Print Reports" cbb.FaceId = 4 cbb.OnAction = "ShowCommandPopupPrintReports" Set cbb = cb.Controls.Add(msoControlButton) cbb.Style = msoButtonIconAndCaption cbb.Caption = "eMail Reports" cbb.FaceId = 258 cbb.OnAction = "CreateAndEmailReports" End Sub "Peter T" wrote: Your code should work fine assuming a commandbar with that name exists. Unless certain, normally best to use On Error resume next ' delete code On Error goto 0 Maybe you have the commandbar "attached" to a workbook which is causing confusion. Regards, Peter T "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not work
Tom,
In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Hi Doug,
I really appreciate the help. I think I have faithfully reproduced your code, but it still is not working. THere is no toolbar appearing when I load the workbook. It appears if I run Sub MakeBar. It does not close when I close the workbook. Do the private subs have to be in a specific module or class module to work? Is there something else that might be happening? Here is my code: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("Dashboard Controls").Delete End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" End With End With End Sub "Doug Glancy" wrote: Tom, In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Tom,
I'm really sorry. I should have mentioned the Activate/Deactivate routines have to be in the ThisWorkbook module. The Make and Delete can be there or in a regular module. Let me know if that gets it going. Doug "Tom Joseph" wrote in message ... Hi Doug, I really appreciate the help. I think I have faithfully reproduced your code, but it still is not working. THere is no toolbar appearing when I load the workbook. It appears if I run Sub MakeBar. It does not close when I close the workbook. Do the private subs have to be in a specific module or class module to work? Is there something else that might be happening? Here is my code: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("Dashboard Controls").Delete End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" End With End With End Sub "Doug Glancy" wrote: Tom, In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Hi Doug.
It works perfectly! Thanks very much. Could you help with one other syntax question? I have several other buttons to add to this command bar, but I am unfamiliar with the convention you used here. Could you tell me how to enable the button I have commented out? Thanks. Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("The Performance Insight„˘ Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" 'Set cbb = cb.Controls.Add(msoControlButton) 'cbb.Style = msoButtonIconAndCaption 'cbb.Caption = "Generate Reports" 'cbb.FaceId = 433 'cbb.OnAction = "ShowCommandPopupGenerateReports" End With End With End Sub "Doug Glancy" wrote: Tom, I'm really sorry. I should have mentioned the Activate/Deactivate routines have to be in the ThisWorkbook module. The Make and Delete can be there or in a regular module. Let me know if that gets it going. Doug "Tom Joseph" wrote in message ... Hi Doug, I really appreciate the help. I think I have faithfully reproduced your code, but it still is not working. THere is no toolbar appearing when I load the workbook. It appears if I run Sub MakeBar. It does not close when I close the workbook. Do the private subs have to be in a specific module or class module to work? Is there something else that might be happening? Here is my code: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("Dashboard Controls").Delete End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" End With End With End Sub "Doug Glancy" wrote: Tom, In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Tom,
The With/End With pair allows you to refer to multiple properties of the control without specifying the control over and over. So here, we repeatedly set the cbb variable to the most recently added button and then use the With/End With to refer to 4 properties of that button. Here is how the code should go: Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="The Performance InsightT Dashboard Controls", Position:=msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = .Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .OnAction = "InitializeDataInput2" End With Set cbb = .Controls.Add(Type:=msoControlButton) With cbb .Style = msoButtonIconAndCaption .Caption = "Generate Reports" .FaceId = 433 .OnAction = "ShowCommandPopupGenerateReports" End With End With End Sub My code last time was sloppy regarding the With's, but here I think it's okay. So in the lines: Set cbb = .Controls.Add(Type:=msoControlButton) the ".Controls" is the same as "cb.Controls" but the "cb" doesn't need to be specified because of the "With cb" above it. In the lines: With cbb .Caption = "Refresh Data", etc, ".Caption" is the same as "cbb.Caption" With/End With saves some resources, especially in some situations, becuase you are only referring to the object once. Looking ahead, you can see that using the same "cbb" variable multiple times to refer to the most recently added control is like a loop. And a loop is just what you want for more complex menus. What many people do for complex menus is a table driven loop, like he http://spreadsheetpage.com/index.php/file/menu_maker/ Have fun! Doug "Tom Joseph" wrote in message ... Hi Doug. It works perfectly! Thanks very much. Could you help with one other syntax question? I have several other buttons to add to this command bar, but I am unfamiliar with the convention you used here. Could you tell me how to enable the button I have commented out? Thanks. Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("The Performance InsightT Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" 'Set cbb = cb.Controls.Add(msoControlButton) 'cbb.Style = msoButtonIconAndCaption 'cbb.Caption = "Generate Reports" 'cbb.FaceId = 433 'cbb.OnAction = "ShowCommandPopupGenerateReports" End With End With End Sub "Doug Glancy" wrote: Tom, I'm really sorry. I should have mentioned the Activate/Deactivate routines have to be in the ThisWorkbook module. The Make and Delete can be there or in a regular module. Let me know if that gets it going. Doug "Tom Joseph" wrote in message ... Hi Doug, I really appreciate the help. I think I have faithfully reproduced your code, but it still is not working. THere is no toolbar appearing when I load the workbook. It appears if I run Sub MakeBar. It does not close when I close the workbook. Do the private subs have to be in a specific module or class module to work? Is there something else that might be happening? Here is my code: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("Dashboard Controls").Delete End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" End With End With End Sub "Doug Glancy" wrote: Tom, In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to close a toolbar on exit of workbook - current code not
Thanks, Doug. I appreciate all the help.
Best, Tom "Doug Glancy" wrote: Tom, The With/End With pair allows you to refer to multiple properties of the control without specifying the control over and over. So here, we repeatedly set the cbb variable to the most recently added button and then use the With/End With to refer to 4 properties of that button. Here is how the code should go: Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="The Performance InsightT Dashboard Controls", Position:=msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = .Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .OnAction = "InitializeDataInput2" End With Set cbb = .Controls.Add(Type:=msoControlButton) With cbb .Style = msoButtonIconAndCaption .Caption = "Generate Reports" .FaceId = 433 .OnAction = "ShowCommandPopupGenerateReports" End With End With End Sub My code last time was sloppy regarding the With's, but here I think it's okay. So in the lines: Set cbb = .Controls.Add(Type:=msoControlButton) the ".Controls" is the same as "cb.Controls" but the "cb" doesn't need to be specified because of the "With cb" above it. In the lines: With cbb .Caption = "Refresh Data", etc, ".Caption" is the same as "cbb.Caption" With/End With saves some resources, especially in some situations, becuase you are only referring to the object once. Looking ahead, you can see that using the same "cbb" variable multiple times to refer to the most recently added control is like a loop. And a loop is just what you want for more complex menus. What many people do for complex menus is a table driven loop, like he http://spreadsheetpage.com/index.php/file/menu_maker/ Have fun! Doug "Tom Joseph" wrote in message ... Hi Doug. It works perfectly! Thanks very much. Could you help with one other syntax question? I have several other buttons to add to this command bar, but I am unfamiliar with the convention you used here. Could you tell me how to enable the button I have commented out? Thanks. Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("The Performance InsightT Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" 'Set cbb = cb.Controls.Add(msoControlButton) 'cbb.Style = msoButtonIconAndCaption 'cbb.Caption = "Generate Reports" 'cbb.FaceId = 433 'cbb.OnAction = "ShowCommandPopupGenerateReports" End With End With End Sub "Doug Glancy" wrote: Tom, I'm really sorry. I should have mentioned the Activate/Deactivate routines have to be in the ThisWorkbook module. The Make and Delete can be there or in a regular module. Let me know if that gets it going. Doug "Tom Joseph" wrote in message ... Hi Doug, I really appreciate the help. I think I have faithfully reproduced your code, but it still is not working. THere is no toolbar appearing when I load the workbook. It appears if I run Sub MakeBar. It does not close when I close the workbook. Do the private subs have to be in a specific module or class module to work? Is there something else that might be happening? Here is my code: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("Dashboard Controls").Delete End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add("Dashboard Controls", _ msoBarFloating, temporary:=True) With cb .Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "Refresh Data" .Style = msoButtonIconAndCaption .Caption = "Refresh Data" .FaceId = 159 .Enabled = True .Visible = True .OnAction = "InitializeDataInput2" End With End With End Sub "Doug Glancy" wrote: Tom, In addition to Peter's advice, I'd put the Make and Delete bar events in the workbook's Activate and Deactivate events, for two reasons. The menu then appears and reappears with the workbook, so if you leave the workbook open, but switch to another workbook, the menu disappears. Also, it eliminates the problem with BeforeClose, which is that if a user Cancels the close, the workbook is still open, but the menu has already been deleted in the BeforeClose event. So I'd do something like this: Option Explicit Private Sub Workbook_Activate() Call MakeBar End Sub Private Sub Workbook_Deactivate() Call DeleteBar End Sub Sub MakeBar() Dim cb As CommandBar Dim cbb As CommandBarButton Call DeleteBar Set cb = Application.CommandBars.Add(Name:="test", Position:=msoBarFloating, temporary:=True) With cb ..Visible = True Set cbb = cb.Controls.Add(Type:=msoControlButton) With cbb .Caption = "testButton" End With End With End Sub Sub DeleteBar() On Error Resume Next Application.CommandBars("test").Delete End Sub hth, Doug "Tom Joseph" wrote in message ... Can someone please help me? The following code is not working. I would like to close a toolbar on exit of a workbook. Thanks. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteBar End Sub Sub DeleteBar() Application.CommandBars("Dashboard Controls").Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open New Workbook / Save and Close Current Workbook | Excel Programming | |||
Trouble removing toolbar - why won't my code work? | Excel Programming | |||
delete toolbar on workbook close | Excel Programming | |||
Close a the current workbook and load another specified workbook | Excel Programming | |||
Close current Workbook after calling macro in other | Excel Programming |