TK,

It works fine on my computer, WinXP, XL 2003. I tried moving back and forth
between workbooks, opening and closing the one with the code, but I can
switch between sheets normally. I can't duplicate the problem.

Sorry. Hopefully Bob, or somebody else, will come up with something.

Doug

"TK" wrote in message
...
Doug

Here it is not much code for all this trouble.
You should be able to paste it into ThisWorkBook
and test.


Private Sub Workbook_Deactivate()
HideToolBar
End Sub

Private Sub Workbook_Activate()
ShowToolBar
End Sub

Sub DisplayMessage()
MsgBox "My Button works, I'm in the Module!"
End Sub

Function ShowToolBar()

Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton

'If the command bar exits, remove it.
On Error Resume Next
Application.CommandBars("cbrCommandBar").Delete


Set cbrCommandBar = Application.CommandBars.Add _
(Name:="cbrCommandBar", _
Position:=msoBarTop, Temporary:=True)

Application.CommandBars("cbrCommandBar").Visible = True

With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)

'Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = "Close Pick List"
.FaceId = 19
.TooltipText = _
"Press me for fun and profit."
.OnAction = "DisplayMessage"
.Tag = "My Big Button"
End With

End With

With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)

' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = "Open Pick List"
.FaceId = 19
.TooltipText = _
"Press me for fun and profit."
.OnAction = "DisplayMessage"
.Tag = "My Big Button"
End With

End With

With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)

'Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = "Summerise Takeoff"
.FaceId = 19
.TooltipText = _
"Press me to summerise"
.OnAction = "DisplayMessage"
.Tag = "My "
End With

End With

End Function

Function HideToolBar()
Application.CommandBars("cbrCommandBar").Visible = False
Application.CommandBars("cbrCommandBar").Delete
End Function

Thanks
TK

"Doug Glancy" wrote:

TK,

I disagree with me too <g. Like you said, I didn't understand the
reference and thought it might be unrelated, and wanted to get you as
much
help as possible. I sure didn't think you were abusing the thread.

That said, I'm clueless, besides the obvious thought that something in
your
code is causing this behavior. Can you post the code?

Doug

"TK" wrote in message
...
Doug

Well you are right about the new threat except you
and Bob know what I'm doing and it would take a lot
of explaining to bring others up to speed. By the page
selector I meant as in using the mouse to pick either
sheet1 sheet2 sheet3. I guess I see now why you didn't
understand the question. Page is not the right term.
It was not my intension to abuse this thread, but
knowing what I was doing I was hoping one of you may
have experienced this and could share some ideas.

Thanks
TK

"Doug Glancy" wrote:

TK,

I don't know what you mean by "Page Selector." If it's an unrelated
question to your original post, you should probably just start a new
thread.

hth,

Doug

"TK" wrote in message
...
Thanks Bob/Doug

Bob
Actually, I'm familiar with some of your procedures having studied
them
from
some of your previous post. With the help of some of your
examples,
Microsoft's
library examples and some specific help from Ron de Bruin I have
all
the
procedures I just needed to learn from where and how to call them
and
as
you
indicate
activate, deactivate is the event. Please read the question in
my
reply
to
Doug.

Doug
Workbook_Acitivate, Workbook_Deactivate in the ThisWorkBook module
calls
the functions properly. Thanks for reminding me of the msgbox
trick I
use
that all
the time it must have been late.

Now that all seems to be working I have another problem. After the
toolbar
is
loaded the page selector will not select another page until
something
is
entered
in a cell and enter is pressed.

I have tried to force a calculation in code, F9 and Ctrl+Alt+F9
without
and
happiness.
Any advise on this one ?

Thanks
TK


"Bob Phillips" wrote:

TK,

Typically, I have a set of menus that I only want associated
with
one
particular workbook. For this I create application events and
delete/hide
the menu if that workbook is deactivated, load/show it when
activated.

Is this any good for you, if so I will give you the code, just
tell
me
your
toolbar name, and where it sits (Off of Tools, or a toolbar).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TK" wrote in message
...
Thanks

Bob / Jim / Doug

In clarification the procedures (functions) work ok and the
Set cbrCommandBar =... Temporary:=True
so if the workbook (wb1)is opened then closed all is good.

However, if another workbook is opened before wb1 is closed
the toolbar is still visible in that workbook.
So I need to toggle the functions when wb1 is the active
workbook and when it is not.

I hope helps you to help me.

Thanks
TK


"Doug Glancy" wrote:

TK,

I'm not sure I'm understanding exactly, but I think you
would be
better
off
actually deleting the toolbar, rather than setting visible
to
false.
Typically the create module also starts by deleting the
toolbar:

on error resume next 'if there's no toolbar you won't get
error
mybar.delete
on error goto 0

This way you don't get the error that comes from creating a
toolbar
that
already exists.

hth,

Doug Glancy

"TK" wrote in message
...
Hi

I have a function in a module that creates a toolbar with
VBA.
"ShowToolBar"
Also a function that deletes (set visual to False).
"HideToolBar"
Now for example:
Wb1 has the modual and then wb2 is opened but wb1 is still
open
but inactive, I would like to delete the toolbar when wb2
opens.
When wb2 becomes inactive or whenever wb1 becomes active
again I would like to restore the toolbar.

I have called the function from Worksheet_Activate
WorkSheetDeactivate; Workbook_Open / Close all without
much happiness.

Any help would be greatly appreciated.