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.

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.