Uh-oh, unexpected behaviour. Commandbar shows up in all workbooks and doesn't close down.

Oh, this is disappointing. I thought this was all done <g>.

I have this code for a commandbar. It works absolutely wonderfully so far,
for the most part:
************************************
Option Explicit

Dim myBar As CommandBar
Dim myButton As CommandBarButton
Const myName As String = "Time Sheet"
Sub Auto_Open()

On Error Resume Next
Application.CommandBars(myName).Delete

Set myBar = Application.CommandBars.Add(myName)
With myBar
.Position = msoBarFloating
.Left = 665
.Top = 145
.Visible = True
.Enabled = True
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Add a new sheet"
.Style = msoButtonIcon
.FaceId = 2054 'or use 366 for a sheet image
.Enabled = True
.OnAction = "NewSheet_Add"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Click to enter a start date."
.Style = msoButtonIcon
.FaceId = 2473
.Enabled = True
.OnAction = "StartDate"
End With
Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
With myButton
.Caption = "Help"
.Style = msoButtonIcon
.FaceId = 49
.Enabled = True
.OnAction = "Help"
End With
End With
End Sub
Sub NewSheet_Add()
Worksheets("TEMPLATE").Copy Before:=Worksheets(1)
Worksheets(1).Visible = xlSheetVisible
End Sub
Sub StartDate()

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter the first working day of the month in
question in the box below:" & vbCrLf & vbCrLf & _
"(Excel is flexible; you can pretty much type any date
format and it'll know what date you mean!)", _
Title:="Overtime Start Date", _
Default:=Format(Date, "mmm dd, yyyy"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("B1").Value = Format(CDate(vResponse), "dddd")
Range("B2").Value = Format(CDate(vResponse), "mmm dd, yyyy")

End Sub
Sub Help()
MsgBox "The Help info is under construction and coming soon!"
End Sub
Sub Auto_Close()
'this runs on closing the workbook
On Error Resume Next
Application.CommandBars("MyToolbar").Delete
End Sub

************************************

I found the Auto_Close code on this msg here:
http://groups.google.ca/group/micros...7d9a4046befcf0
with title "Adding & Removing Custom CommandBars". I've obviously missed
something because the commandbar persists even after closing the workbook.
And I prefer doing something upon closing workbook rather than relying on
user closing bar.

The other difficult behaviour to contend with is that once this file is open
and the commandbar is available, if you click to any other open workbook,
this floating toolbar comes along for the ride! <sigh> This was not what I
envisioned when I went to the trouble of replacing all my buttons and
assigned macros with this toolbar <g>. It's really great to have this, but
not with the behaviour as it is at present.

I'm guessing I've done something wrong with the closing code., and is there
anything that can be done to have access to this toolbar _only_ when the
pertinent workbook is open?

Thank you!

You called your toolbar: Time Sheet

But in the auto_close procedure, you used:
Application.CommandBars("MyToolbar").Delete

So
Application.CommandBars("time sheet").Delete
or
Application.CommandBars(myName).Delete

(since you declared that constant.




StargateFanFromWork wrote:
>
> Oh, this is disappointing. I thought this was all done <g>.
>
> I have this code for a commandbar. It works absolutely wonderfully so far,
> for the most part:
> ************************************
> Option Explicit
>
> Dim myBar As CommandBar
> Dim myButton As CommandBarButton
> Const myName As String = "Time Sheet"
> Sub Auto_Open()
>
> On Error Resume Next
> Application.CommandBars(myName).Delete
>
> Set myBar = Application.CommandBars.Add(myName)
> With myBar
> .Position = msoBarFloating
> .Left = 665
> .Top = 145
> .Visible = True
> .Enabled = True
> Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
> With myButton
> .Caption = "Add a new sheet"
> .Style = msoButtonIcon
> .FaceId = 2054 'or use 366 for a sheet image
> .Enabled = True
> .OnAction = "NewSheet_Add"
> End With
> Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
> With myButton
> .Caption = "Click to enter a start date."
> .Style = msoButtonIcon
> .FaceId = 2473
> .Enabled = True
> .OnAction = "StartDate"
> End With
> Set myButton = .Controls.Add(Type:=msoControlButton, ID:=23)
> With myButton
> .Caption = "Help"
> .Style = msoButtonIcon
> .FaceId = 49
> .Enabled = True
> .OnAction = "Help"
> End With
> End With
> End Sub
> Sub NewSheet_Add()
> Worksheets("TEMPLATE").Copy Before:=Worksheets(1)
> Worksheets(1).Visible = xlSheetVisible
> End Sub
> Sub StartDate()
>
> Dim vResponse As Variant
> Do
> vResponse = Application.InputBox( _
> Prompt:="Enter the first working day of the month in
> question in the box below:" & vbCrLf & vbCrLf & _
> "(Excel is flexible; you can pretty much type any date
> format and it'll know what date you mean!)", _
> Title:="Overtime Start Date", _
> Default:=Format(Date, "mmm dd, yyyy"), _
> Type:=2)
> If vResponse = False Then Exit Sub 'User cancelled
> Loop Until IsDate(vResponse)
> Range("B1").Value = Format(CDate(vResponse), "dddd")
> Range("B2").Value = Format(CDate(vResponse), "mmm dd, yyyy")
>
> End Sub
> Sub Help()
> MsgBox "The Help info is under construction and coming soon!"
> End Sub
> Sub Auto_Close()
> 'this runs on closing the workbook
> On Error Resume Next
> Application.CommandBars("MyToolbar").Delete
> End Sub
>
> ************************************
>
> I found the Auto_Close code on this msg here:
> http://groups.google.ca/group/micros...7d9a4046befcf0
> with title "Adding & Removing Custom CommandBars". I've obviously missed
> something because the commandbar persists even after closing the workbook.
> And I prefer doing something upon closing workbook rather than relying on
> user closing bar.
>
> The other difficult behaviour to contend with is that once this file is open
> and the commandbar is available, if you click to any other open workbook,
> this floating toolbar comes along for the ride! <sigh> This was not what I
> envisioned when I went to the trouble of replacing all my buttons and
> assigned macros with this toolbar <g>. It's really great to have this, but
> not with the behaviour as it is at present.
>
> I'm guessing I've done something wrong with the closing code., and is there
> anything that can be done to have access to this toolbar _only_ when the
> pertinent workbook is open?
>
> Thank you!

--

Dave Peterson

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> You called your toolbar: Time Sheet
>
> But in the auto_close procedure, you used:
> Application.CommandBars("MyToolbar").Delete
>
> So
> Application.CommandBars("time sheet").Delete
> or
> Application.CommandBars(myName).Delete
>
> (since you declared that constant.

[snip]

Oh, dear <rofl>. I didn't catch on to that. I tried to modify the code I
found but I missed this naming thing from the looks of it. So many
different things to tackle in all this code and to try and figure out <g>.
I got this to work just now before coming back here to see your msg so was
coming to add that to post <g>. It was another example of the Auto_Close
for a custom commandbar and I see that I stumbled onto the right way of
naming everything this second time around as the toolbar finally does close
down now when I close the sheet <g>. Glad you put my attention to it,
though, as I wouldn't have figured out why I'd gotten it to work _this_ time
<lol>!

Also, re the other issue in the subject line, I found an example of a custom
floating menu (different enough syntax from the commanbar to really throw
me), that does do the hide trick on an inactive sheet while making it
visible when the sheet is active. That is so kewl. I tried working with
that code but no go, so will keep googling in the archives for other posts
with examples for this. (The particular floating menu I speak of is a file
I dl and wasn't code I'd found written up in one of these ngs.)

But must admit that I'm getting pretty tired. I worked till 8:30 p.m. last
night and then continued working in Excel when I got home for another couple
of hours so I'm going to give this a rest for now and tackle this
hide/unhide behaviour later tonight (if I'm up to it <g>). It'll be easier
when I'm fresher.

Thank you! Slowly getting there. :oD

Learning new stuff _should_ be challenging. But think of the benefits you'll
get!

StargateFanFromWork wrote:
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You called your toolbar: Time Sheet
> >
> > But in the auto_close procedure, you used:
> > Application.CommandBars("MyToolbar").Delete
> >
> > So
> > Application.CommandBars("time sheet").Delete
> > or
> > Application.CommandBars(myName).Delete
> >
> > (since you declared that constant.
>
> [snip]
>
> Oh, dear <rofl>. I didn't catch on to that. I tried to modify the code I
> found but I missed this naming thing from the looks of it. So many
> different things to tackle in all this code and to try and figure out <g>.
> I got this to work just now before coming back here to see your msg so was
> coming to add that to post <g>. It was another example of the Auto_Close
> for a custom commandbar and I see that I stumbled onto the right way of
> naming everything this second time around as the toolbar finally does close
> down now when I close the sheet <g>. Glad you put my attention to it,
> though, as I wouldn't have figured out why I'd gotten it to work _this_ time
> <lol>!
>
> Also, re the other issue in the subject line, I found an example of a custom
> floating menu (different enough syntax from the commanbar to really throw
> me), that does do the hide trick on an inactive sheet while making it
> visible when the sheet is active. That is so kewl. I tried working with
> that code but no go, so will keep googling in the archives for other posts
> with examples for this. (The particular floating menu I speak of is a file
> I dl and wasn't code I'd found written up in one of these ngs.)
>
> But must admit that I'm getting pretty tired. I worked till 8:30 p.m. last
> night and then continued working in Excel when I got home for another couple
> of hours so I'm going to give this a rest for now and tackle this
> hide/unhide behaviour later tonight (if I'm up to it <g>). It'll be easier
> when I'm fresher.
>
> Thank you! Slowly getting there. :oD

--

Dave Peterson

In the Workbook deactivate event, set the visible property of the
commandbar to false. In the workbook activate event, set the visible
property of the commandbar to true.

Your correct that it is often best to step away from the problem and rest.
Often, in these interludes, these type solutions will become quite obvious.

--
Regards,
Tom Ogilvy

"StargateFanFromWork" <[email protected]> wrote in message
news:%[email protected]...
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > You called your toolbar: Time Sheet
> >
> > But in the auto_close procedure, you used:
> > Application.CommandBars("MyToolbar").Delete
> >
> > So
> > Application.CommandBars("time sheet").Delete
> > or
> > Application.CommandBars(myName).Delete
> >
> > (since you declared that constant.
>
> [snip]
>
> Oh, dear <rofl>. I didn't catch on to that. I tried to modify the code I
> found but I missed this naming thing from the looks of it. So many
> different things to tackle in all this code and to try and figure out <g>.
> I got this to work just now before coming back here to see your msg so was
> coming to add that to post <g>. It was another example of the Auto_Close
> for a custom commandbar and I see that I stumbled onto the right way of
> naming everything this second time around as the toolbar finally does
close
> down now when I close the sheet <g>. Glad you put my attention to it,
> though, as I wouldn't have figured out why I'd gotten it to work _this_
time
> <lol>!
>
> Also, re the other issue in the subject line, I found an example of a
custom
> floating menu (different enough syntax from the commanbar to really throw
> me), that does do the hide trick on an inactive sheet while making it
> visible when the sheet is active. That is so kewl. I tried working with
> that code but no go, so will keep googling in the archives for other posts
> with examples for this. (The particular floating menu I speak of is a
file
> I dl and wasn't code I'd found written up in one of these ngs.)
>
> But must admit that I'm getting pretty tired. I worked till 8:30 p.m.
last
> night and then continued working in Excel when I got home for another
couple
> of hours so I'm going to give this a rest for now and tackle this
> hide/unhide behaviour later tonight (if I'm up to it <g>). It'll be
easier
> when I'm fresher.
>
> Thank you! Slowly getting there. :oD
>
>

On Thu, 8 Dec 2005 21:22:59 -0500, "Tom Ogilvy" <[email protected]>
wrote:

> In the Workbook deactivate event, set the visible property of the
>commandbar to false. In the workbook activate event, set the visible
>property of the commandbar to true.
>
>Your correct that it is often best to step away from the problem and rest.
>Often, in these interludes, these type solutions will become quite obvious.

Yeah, even though I'm in that state these days where I feel like I'm
pounding stuff through a narrow-necked bottle - meaning the way of
getting things into my brain, there's so much detail to work with!
<lol>

Thanks, knowing the specific things to look for, Workbook deactivate
and Workbook activate, really helped. I googled in the archives again
and found a couple of examples. Nothing I did worked until I put the
code in the "This Workbook" instead of the module after finding a
reference to that, and then changing the (myName) to ("Time Sheet").
I'm guessing that (myName) works only in the module because that's
where the constant is stated. And that since deactivate and activate
are outside of this in "ThisWorkbook", that's why the specific name is
needed instead (?).

Despite the time it took and the usual bits of frustration along the
way <g>, this was fun. I'm heartened because I seem to be having a
little easier time of it when I'm wading through all sorts of example
code in the archives looking for what I'm trying to do <g>.

Have a great day everyone! :oD

[snip]

You could make your constant visible to any module:

Public Const myName As String = "Time Sheet"

Search for "Understanding Scope and Visibility" in VBA's help.



StargateFan wrote:
>
> On Thu, 8 Dec 2005 21:22:59 -0500, "Tom Ogilvy" <[email protected]>
> wrote:
>
> > In the Workbook deactivate event, set the visible property of the
> >commandbar to false. In the workbook activate event, set the visible
> >property of the commandbar to true.
> >
> >Your correct that it is often best to step away from the problem and rest.
> >Often, in these interludes, these type solutions will become quite obvious.
>
> Yeah, even though I'm in that state these days where I feel like I'm
> pounding stuff through a narrow-necked bottle - meaning the way of
> getting things into my brain, there's so much detail to work with!
> <lol>
>
> Thanks, knowing the specific things to look for, Workbook deactivate
> and Workbook activate, really helped. I googled in the archives again
> and found a couple of examples. Nothing I did worked until I put the
> code in the "This Workbook" instead of the module after finding a
> reference to that, and then changing the (myName) to ("Time Sheet").
> I'm guessing that (myName) works only in the module because that's
> where the constant is stated. And that since deactivate and activate
> are outside of this in "ThisWorkbook", that's why the specific name is
> needed instead (?).
>
> Despite the time it took and the usual bits of frustration along the
> way <g>, this was fun. I'm heartened because I seem to be having a
> little easier time of it when I'm wading through all sorts of example
> code in the archives looking for what I'm trying to do <g>.
>
> Have a great day everyone! :oD
>
> [snip]

--

Dave Peterson