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

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" <(E-Mail Removed)> wrote in message
news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:23BB51FA-531A-4D0D-9A18-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
>> > 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:23BB51FA-531A-4D0D-9A18-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:9F986661-13C5-462B-9AB6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
>> > 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:9F986661-13C5-462B-9AB6-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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 here:

http://spreadsheetpage.com/index.php/file/menu_maker/

Have fun!

Doug

"Tom Joseph" <(E-Mail Removed)> wrote in message
news:9D7C6B95-0073-4A24-BD40-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:9F986661-13C5-462B-9AB6-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:74504513-41DA-4F4F-9F52-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>