Commandbar on thisworkbook only

When creating a commandbar with the code below it is only a temporarily
commandbar and that is great, however it is only temp in that sense that you
have to close the Excel application down and reopen before the commandbar
removed.

Is there away to have the commandbar removed as soon as you close the
worksheet that has the macro and command?


Dim oCb As CommandBar
Dim oCtl As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl = .Controls.Add( _
Type:=msoControlButton, _
temporary:=True)
oCtl.Caption = "myButton"
oCtl.Style = msoButtonCaption
oCtl.OnAction = "myMacro"
End With


Thank you in advance for you help.

Rene

The answer is yes. However, if you search this discussion for "GB Toolbar"
you will see a thread that has 28+ messages. Look towards the end of the
thread for a more up-to-date version of the code. You should be able to snip
out the Tool_Bar_Delete (Or similiarly named) section to perform what you
want. You will also need to add some code to the ThisWorkbook_Close section
to force destruction of the toolbar.

The "final" version posted there (by me) allows for multiple toolbars to be
created, one or more for each worksheet, or of course none to be present on a
worksheet. There is an upper programmed limit of 10, but by removing one and
inserting another that upper limit could be "exceeded." (I have not
implemented this option in my code yet however.)


"Rene Petersen" wrote:

> When creating a commandbar with the code below it is only a temporarily
> commandbar and that is great, however it is only temp in that sense that you
> have to close the Excel application down and reopen before the commandbar
> removed.
>
> Is there away to have the commandbar removed as soon as you close the
> worksheet that has the macro and command?
>
>
> Dim oCb As CommandBar
> Dim oCtl As CommandBarButton
>
> Set oCb = Application.CommandBars("Worksheet Menu Bar")
> With oCb
> Set oCtl = .Controls.Add( _
> Type:=msoControlButton, _
> temporary:=True)
> oCtl.Caption = "myButton"
> oCtl.Style = msoButtonCaption
> oCtl.OnAction = "myMacro"
> End With
>
>
> Thank you in advance for you help.
>
> Rene

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu Bar") _
.Controls("myButton").Delete
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rene Petersen" <[email protected]> wrote in message
news:[email protected]...
> When creating a commandbar with the code below it is only a temporarily
> commandbar and that is great, however it is only temp in that sense that
you
> have to close the Excel application down and reopen before the commandbar
> removed.
>
> Is there away to have the commandbar removed as soon as you close the
> worksheet that has the macro and command?
>
>
> Dim oCb As CommandBar
> Dim oCtl As CommandBarButton
>
> Set oCb = Application.CommandBars("Worksheet Menu Bar")
> With oCb
> Set oCtl = .Controls.Add( _
> Type:=msoControlButton, _
> temporary:=True)
> oCtl.Caption = "myButton"
> oCtl.Style = msoButtonCaption
> oCtl.OnAction = "myMacro"
> End With
>
>
> Thank you in advance for you help.
>
> Rene

Actually, this option would remove the button, but not the commandbar.
Obviously to remove the commandbar, just remove from the below code the
..Controls("myButton") portion, and the commandbar will be deleted. it will
error if the user has also deleted it from the View->Toolbars section, so
might want some error controls. Just a thought.

"Bob Phillips" wrote:

> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Application.CommandBars("Worksheet Menu Bar") _
> .Controls("myButton").Delete
> End Sub
>
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Rene Petersen" <[email protected]> wrote in message
> news:[email protected]...
> > When creating a commandbar with the code below it is only a temporarily
> > commandbar and that is great, however it is only temp in that sense that
> you
> > have to close the Excel application down and reopen before the commandbar
> > removed.
> >
> > Is there away to have the commandbar removed as soon as you close the
> > worksheet that has the macro and command?
> >
> >
> > Dim oCb As CommandBar
> > Dim oCtl As CommandBarButton
> >
> > Set oCb = Application.CommandBars("Worksheet Menu Bar")
> > With oCb
> > Set oCtl = .Controls.Add( _
> > Type:=msoControlButton, _
> > temporary:=True)
> > oCtl.Caption = "myButton"
> > oCtl.Style = msoButtonCaption
> > oCtl.OnAction = "myMacro"
> > End With
> >
> >
> > Thank you in advance for you help.
> >
> > Rene
>
>
>

Well I ran the create code and it only created a button, so I gave him code
just to remove that button.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GB" <[email protected]> wrote in message
news:[email protected]...
> Actually, this option would remove the button, but not the commandbar.
> Obviously to remove the commandbar, just remove from the below code the
> .Controls("myButton") portion, and the commandbar will be deleted. it will
> error if the user has also deleted it from the View->Toolbars section, so
> might want some error controls. Just a thought.
>
> "Bob Phillips" wrote:
>
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Application.CommandBars("Worksheet Menu Bar") _
> > .Controls("myButton").Delete
> > End Sub
> >
> >
> > 'This is workbook event code.
> > 'To input this code, right click on the Excel icon on the worksheet
> > '(or next to the File menu if you maximise your workbooks),
> > 'select View Code from the menu, and paste the code
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Rene Petersen" <[email protected]> wrote in
message
> > news:[email protected]...
> > > When creating a commandbar with the code below it is only a
temporarily
> > > commandbar and that is great, however it is only temp in that sense
that
> > you
> > > have to close the Excel application down and reopen before the
commandbar
> > > removed.
> > >
> > > Is there away to have the commandbar removed as soon as you close the
> > > worksheet that has the macro and command?
> > >
> > >
> > > Dim oCb As CommandBar
> > > Dim oCtl As CommandBarButton
> > >
> > > Set oCb = Application.CommandBars("Worksheet Menu Bar")
> > > With oCb
> > > Set oCtl = .Controls.Add( _
> > > Type:=msoControlButton, _
> > > temporary:=True)
> > > oCtl.Caption = "myButton"
> > > oCtl.Style = msoButtonCaption
> > > oCtl.OnAction = "myMacro"
> > > End With
> > >
> > >
> > > Thank you in advance for you help.
> > >
> > > Rene
> >
> >
> >

I can respect that.

It was my understanding that a Commandbar (toolbar) was also created. In my
initial reply, I couldn't remember what code would delete the commandbar, but
knew that I had programmed it somewhere that was readable. You were able to
straight up answer the question. I think that Rene should be good to go, we
just need to await some sort of indication of such.

"Bob Phillips" wrote:

> Well I ran the create code and it only created a button, so I gave him code
> just to remove that button.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "GB" <[email protected]> wrote in message
> news:[email protected]...
> > Actually, this option would remove the button, but not the commandbar.
> > Obviously to remove the commandbar, just remove from the below code the
> > .Controls("myButton") portion, and the commandbar will be deleted. it will
> > error if the user has also deleted it from the View->Toolbars section, so
> > might want some error controls. Just a thought.
> >
> > "Bob Phillips" wrote:
> >
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Application.CommandBars("Worksheet Menu Bar") _
> > > .Controls("myButton").Delete
> > > End Sub
> > >
> > >
> > > 'This is workbook event code.
> > > 'To input this code, right click on the Excel icon on the worksheet
> > > '(or next to the File menu if you maximise your workbooks),
> > > 'select View Code from the menu, and paste the code
> > >
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Rene Petersen" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > When creating a commandbar with the code below it is only a
> temporarily
> > > > commandbar and that is great, however it is only temp in that sense
> that
> > > you
> > > > have to close the Excel application down and reopen before the
> commandbar
> > > > removed.
> > > >
> > > > Is there away to have the commandbar removed as soon as you close the
> > > > worksheet that has the macro and command?
> > > >
> > > >
> > > > Dim oCb As CommandBar
> > > > Dim oCtl As CommandBarButton
> > > >
> > > > Set oCb = Application.CommandBars("Worksheet Menu Bar")
> > > > With oCb
> > > > Set oCtl = .Controls.Add( _
> > > > Type:=msoControlButton, _
> > > > temporary:=True)
> > > > oCtl.Caption = "myButton"
> > > > oCtl.Style = msoButtonCaption
> > > > oCtl.OnAction = "myMacro"
> > > > End With
> > > >
> > > >
> > > > Thank you in advance for you help.
> > > >
> > > > Rene
> > >
> > >
> > >
>
>
>

Okay, I also wrote a little code to show all of the names of the commandbars
that are "currently" in Excel. The name used by Rene for the toolbar that is
getting the button, is a default toolbar. It would, therefore, be unwise to
delete that commandbar.

The confusion (on my part) stemmed from the discussion that a commandbar was
created. It wasn't or normally isn't created, it was just used. So your
code of deleting the icon would be the appropriate method to use to restore
this particular commandbar back to it's original state. If a new commandbar
had been created, then it would have been necessary to delete the whole
commandbar, not just the icon(s) created on it.



"GB" wrote:

> I can respect that.
>
> It was my understanding that a Commandbar (toolbar) was also created. In my
> initial reply, I couldn't remember what code would delete the commandbar, but
> knew that I had programmed it somewhere that was readable. You were able to
> straight up answer the question. I think that Rene should be good to go, we
> just need to await some sort of indication of such.
>
> "Bob Phillips" wrote:
>
> > Well I ran the create code and it only created a button, so I gave him code
> > just to remove that button.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "GB" <[email protected]> wrote in message
> > news:[email protected]...
> > > Actually, this option would remove the button, but not the commandbar.
> > > Obviously to remove the commandbar, just remove from the below code the
> > > .Controls("myButton") portion, and the commandbar will be deleted. it will
> > > error if the user has also deleted it from the View->Toolbars section, so
> > > might want some error controls. Just a thought.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Application.CommandBars("Worksheet Menu Bar") _
> > > > .Controls("myButton").Delete
> > > > End Sub
> > > >
> > > >
> > > > 'This is workbook event code.
> > > > 'To input this code, right click on the Excel icon on the worksheet
> > > > '(or next to the File menu if you maximise your workbooks),
> > > > 'select View Code from the menu, and paste the code
> > > >
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Rene Petersen" <[email protected]> wrote in
> > message
> > > > news:[email protected]...
> > > > > When creating a commandbar with the code below it is only a
> > temporarily
> > > > > commandbar and that is great, however it is only temp in that sense
> > that
> > > > you
> > > > > have to close the Excel application down and reopen before the
> > commandbar
> > > > > removed.
> > > > >
> > > > > Is there away to have the commandbar removed as soon as you close the
> > > > > worksheet that has the macro and command?
> > > > >
> > > > >
> > > > > Dim oCb As CommandBar
> > > > > Dim oCtl As CommandBarButton
> > > > >
> > > > > Set oCb = Application.CommandBars("Worksheet Menu Bar")
> > > > > With oCb
> > > > > Set oCtl = .Controls.Add( _
> > > > > Type:=msoControlButton, _
> > > > > temporary:=True)
> > > > > oCtl.Caption = "myButton"
> > > > > oCtl.Style = msoButtonCaption
> > > > > oCtl.OnAction = "myMacro"
> > > > > End With
> > > > >
> > > > >
> > > > > Thank you in advance for you help.
> > > > >
> > > > > Rene
> > > >
> > > >
> > > >
> >
> >
> >