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.

Where did you put those event procedures, in the sheet class and workbook
class modules, or a normal code module?

--

HTH

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


"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.

Hi Bob

normal module If I unstand (insert Module) now reads module1



"Bob Phillips" wrote:

Where did you put those event procedures, in the sheet class and workbook
class modules, or a normal code module?

--

HTH

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


"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.

Wrong place.

Worksheet_Activate/Deactivate goes in that worksheets code module,
Workbook_Open/BeforeClose goes in ThisWorkbook.

--

HTH

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


"TK" wrote in message
...
Hi Bob

normal module If I unstand (insert Module) now reads module1



"Bob Phillips" wrote:

Where did you put those event procedures, in the sheet class and
workbook
class modules, or a normal code module?

--

HTH

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


"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.

You can run code similar to this from a regular module but you need to use
different names

Public Sub auto_open()

End Sub

Public Sub auto_close()

End Sub

and there is nothing for a sheet event. This code is handy if you want to
export the module and use it in other workbooks...

HTH

"TK" wrote:

Hi Bob

normal module If I unstand (insert Module) now reads module1



"Bob Phillips" wrote:

Where did you put those event procedures, in the sheet class and workbook
class modules, or a normal code module?

--

HTH

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


"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.

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.

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.

TK,

You need to use the Workbook_Acitivate, Workbook_Deactivate and
Workbook_BeforeClose events in the ThisWorkbook module. I think that's all
you need, but I always have to re-educate myself when creating menus. I've
found it useful to create these different events and put simple msgbox
messages in them, e.g., "book1 deactivated", "sheet1 activated", etc., so I
can understand when they occur. Doing so will show that switching between
workbooks does not fire the Worksheet_Deactivate event. On the other hand,
the Workbook_Activate event is fired when a workbook is opened, so I don't
think you need to use the Workbook_Open event for menu creation.

hth,

Doug Glancy

"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.

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.

Thanks Bob/Doug

Bob
Actually, Im familiar with some of your procedures having studied them from
some of your previous post. With the help of some of your examples,
Microsofts
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.

TK,

I think that you need to put the Hide code in the Workbook_Deactivate event
as well as Workbook_BeforeClose, and then the Show in the Workbook_Open and
Workbook_Activate events.

But, crucially, as I said before, these event procedures must go in the
ThisWorkbook code module.

--

HTH

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


"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.

Bob

These are in ThisWookBook

Private Sub Workbook_Deactivate()
HideToolBar
End Sub

Private Sub Workbook_Activate()
ShowToolBar
End SubTK

The functions are in €œModule1€� not a class module

They work as expected whether the function are
in ThisWorkBook or the Module as they should.
I tested them in both.

The problem I now have is the page selector will not
work until something is entered in a cell, the toolbar works.

Thanks


"Bob Phillips" wrote:

TK,

I think that you need to put the Hide code in the Workbook_Deactivate event
as well as Workbook_BeforeClose, and then the Show in the Workbook_Open and
Workbook_Activate events.

But, crucially, as I said before, these event procedures must go in the
ThisWorkbook code module.

--

HTH

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


"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.

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.

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.

What do you mean by page selector, first time this has cropped up?

--

HTH

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


"TK" wrote in message
...
Bob

These are in ThisWookBook

Private Sub Workbook_Deactivate()
HideToolBar
End Sub

Private Sub Workbook_Activate()
ShowToolBar
End SubTK

The functions are in "Module1" not a class module

They work as expected whether the function are
in ThisWorkBook or the Module as they should.
I tested them in both.

The problem I now have is the page selector will not
work until something is entered in a cell, the toolbar works.

Thanks


"Bob Phillips" wrote:

TK,

I think that you need to put the Hide code in the Workbook_Deactivate
event
as well as Workbook_BeforeClose, and then the Show in the Workbook_Open
and
Workbook_Activate events.

But, crucially, as I said before, these event procedures must go in the
ThisWorkbook code module.

--

HTH

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


"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.

TK,

I disagree with Doug on this, keeping to this thread seems right to me.

Are you saying that when you click on the tab that won't trigger a workbook
event. What do you want to happen when you change sheets?

Can I also just check what works.

- does the workbook_open/beforeclose act correctly on the toolbars?
- does the workbook_activate/deactivate work correctly on the toolbars?
- for all workbooks?

So what else do you need to happen?

--

HTH

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


"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.

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.

Bob Phillips" wrote:

TK,

I disagree with Doug on this, keeping to this thread seems right to me.

Are you saying that when you click on the tab that won't trigger a workbook
event. What do you want to happen when you change sheets?

Nothing, just to change sheets

Can I also just check what works.

- does the workbook_open/beforeclose act correctly on the toolbars?

Not to sure what you mean. I'm not using those events.
I started by using the Workbook_Open but the
WorkBook_Activate recalled the function even when the
Wookbook was initially opened so it wasn't necessary.


- does the workbook_activate/deactivate work correctly on the toolbars?
- for all workbooks?

Perfect, and the toolbar buttons work (they call dummy events)


So what else do you need to happen?


Nothing, the only problem at this time is when the WorkBook
is activated either initially or another workbook is opened and
then closed and the activate event calls ShowTooBars if you click
on a different sheet it does nothing. If you enter data in a cell or
move the page (as in adjust it) the selector then works.

Other Notes:
The sheet selector works when I start Excel so it is not Excel.
This is the only procedure in the WorkBook Im testing in so
there should not be any conflicts.
I removed
€œ On Error Resume Next€�
€œApplication.CommandBars("cbrCommandBar").Delete €œ
to try to fail the procedure but it didnt fail.
It has to be in the procedure but I cant seem to find
the problem.

Thanks
TK

--

HTH

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


"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.

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.

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.

Doug

Thanks for testing that. After your last reply I tested the Workbook
on another computer with Windows 98 (I know I used it in the cave)
with Excel 2000 and it worked as expected. I also tested it on another
computer with Windows XP Home the same as on this computer and
it also failed or froze the sheet selector or whatever its called.
Unfortunately the problem instead of getting less difficult got more
difficult.

Thanks
Tk

"Doug Glancy" wrote:

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.