I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either. Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

I haven't seen this on Walkenbach, but this doesn't seem right.

I am assuming that the ABuse commandbar applies to a single workbook? If so,
why are you using application events which would apply to all workbooks? And
as it applies to all workbooks, why would you want to hide it when any, or
all, workbooks close.

I would have thought that you should have some code to build or make visible
the Abuse commandbar, and some code to hide it. If it applies to a single
workbook, the best places would be the workbook open and beforeclose
events.

--

HTH

RP

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either. Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook

and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select your
withevents var' name in the top middle dropdown, then find your event in the
top right dropdown.

Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either. Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

Bob Phillips wrote:
I haven't seen this on Walkenbach, but this doesn't seem right.

I am assuming that the ABuse commandbar applies to a single workbook? If so,
why are you using application events which would apply to all workbooks? And
as it applies to all workbooks, why would you want to hide it when any, or
all, workbooks close.

Thanks for pointing that out. I got confused between "Application"
level and "Document" level. I went back and re-read Walkenbach, and it
says "Application", which is what I thought I wanted. I guess when it's
only a particular workbook, it's document level. I was thinking that a
worksheet would be document level, and a workbook would be application
level.

I would have thought that you should have some code to build or make visible
the Abuse commandbar, and some code to hide it. If it applies to a single
workbook, the best places would be the workbook open and beforeclose
events.


I already have the code to show the toolbar when I select the
appropriate sheet and hide the toolbar when I move to another sheet,
and it works great. Now I'm trying to close all the custom toolbars,
not the Standard or Formatting toolbars, when I close the workbook.

--

HTH

RP

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either. Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select your
withevents var' name in the top middle dropdown, then find your event in the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either. Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

Also, in my experience, simply hiding the toolbar does not remove it from
Excel considering it a part of all toolbars available to the user.
(View-Toolbars). If a user opts to go to this location the toolbar will be
displayed but when selecting options from the toolbar either errors will be
created or the workbook to which the toolbars are associated will be opened
and the actions performed. I can't remember which of those two occur.

Therefore I have found that it is necessary to delete the toolbar from excel
when closing the applicable workbook, not just hiding it.

There is reference to this in a thread that davegb was working on before.

"Peter T" wrote:

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.

GB wrote:
Also, in my experience, simply hiding the toolbar does not remove it from
Excel considering it a part of all toolbars available to the user.
(View-Toolbars). If a user opts to go to this location the toolbar will be
displayed but when selecting options from the toolbar either errors will be
created or the workbook to which the toolbars are associated will be opened
and the actions performed. I can't remember which of those two occur.

Therefore I have found that it is necessary to delete the toolbar from excel
when closing the applicable workbook, not just hiding it.

There is reference to this in a thread that davegb was working on before.

"Peter T" wrote:

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.






Thanks for all your replies. I'm working on 2 parallel solutions to
this problem. I started, with your help, GB, in another thread, with
creating and deleting the toolbars entirely, but that was getting very
complicated and was obviously not going to be done quickly or easily. I
decided to start by getting the toolbars just to Show and Hide as
needed. I realize this approach would leave open the possibility that
an end-user could access those hidden toolbars and try to use them. I
tested this out, and found that all they do is return the standard VBA
error message, and since I have the code protected, their only option
is "Cancel", which returns them to the spreadsheet, no damage done.
However, considering the Excel knowledge level of the vast majority of
my users, this in highly unlikely. But I still like the other solution
better, and will continue working on it. Meanwhile, if I get the call
that they need the next iteration of the workbook, I can give them this
one.
For now, my solution, which works, is as follows:
In "This Workbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideCustomTbars
End Sub

In a standard module called "Toolbars", I have:

Sub HideCustomTbars()
Dim cTBar As CommandBar

For Each cTBar In Application.CommandBars
If cTBar.Type = msoBarTypeNormal Then
If Not cTBar.Name = "Standard" Then
If Not cTBar.Name = "Formatting" Then
cTBar.Visible = False
End If
End If
End If
Next cTBar
End Sub

Does anyone see any potential pitfalls, other than those previously
noted, in this solution?

I will continue, as time permits, with the other, more elegant and
safer solution.

Not sure how well the following would work. I would recommend, since one
never knows what toolbars a user has gone through the effort of getting on
their screen. (Hopefully not ones that would defeat your code.) that you
either somehow in memory maintain a list of the toolbars that you have
created, like in a CommandBars object, to which you add your toolbars when
you create them for normal usage, or like some of the other code where you
have a "list" of set toolbar names. This way you just iterate on your known
toolbars (i.e. the real custom ones.) and hide only those toolbars that you
as the programmer have created. Not to interfere with other programming and
user options.

On the other hand if you need a toolbar to become visible to work, I
wouldn't see a problem with being intrusive and forcing it to appear. :)

"davegb" wrote:


GB wrote:
Also, in my experience, simply hiding the toolbar does not remove it from
Excel considering it a part of all toolbars available to the user.
(View-Toolbars). If a user opts to go to this location the toolbar will be
displayed but when selecting options from the toolbar either errors will be
created or the workbook to which the toolbars are associated will be opened
and the actions performed. I can't remember which of those two occur.

Therefore I have found that it is necessary to delete the toolbar from excel
when closing the applicable workbook, not just hiding it.

There is reference to this in a thread that davegb was working on before.

"Peter T" wrote:

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.






Thanks for all your replies. I'm working on 2 parallel solutions to
this problem. I started, with your help, GB, in another thread, with
creating and deleting the toolbars entirely, but that was getting very
complicated and was obviously not going to be done quickly or easily. I
decided to start by getting the toolbars just to Show and Hide as
needed. I realize this approach would leave open the possibility that
an end-user could access those hidden toolbars and try to use them. I
tested this out, and found that all they do is return the standard VBA
error message, and since I have the code protected, their only option
is "Cancel", which returns them to the spreadsheet, no damage done.
However, considering the Excel knowledge level of the vast majority of
my users, this in highly unlikely. But I still like the other solution
better, and will continue working on it. Meanwhile, if I get the call
that they need the next iteration of the workbook, I can give them this
one.
For now, my solution, which works, is as follows:
In "This Workbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideCustomTbars
End Sub

In a standard module called "Toolbars", I have:

Sub HideCustomTbars()
Dim cTBar As CommandBar

For Each cTBar In Application.CommandBars
If cTBar.Type = msoBarTypeNormal Then
If Not cTBar.Name = "Standard" Then
If Not cTBar.Name = "Formatting" Then
cTBar.Visible = False
End If
End If
End If
Next cTBar
End Sub

Does anyone see any potential pitfalls, other than those previously
noted, in this solution?

I will continue, as time permits, with the other, more elegant and
safer solution.

GB wrote:
Not sure how well the following would work. I would recommend, since one
never knows what toolbars a user has gone through the effort of getting on
their screen. (Hopefully not ones that would defeat your code.) that you
either somehow in memory maintain a list of the toolbars that you have
created, like in a CommandBars object, to which you add your toolbars when
you create them for normal usage, or like some of the other code where you
have a "list" of set toolbar names. This way you just iterate on your known
toolbars (i.e. the real custom ones.) and hide only those toolbars that you
as the programmer have created. Not to interfere with other programming and
user options.

On the other hand if you need a toolbar to become visible to work, I
wouldn't see a problem with being intrusive and forcing it to appear. :)

"davegb" wrote:


GB wrote:

It wouldn't be difficult for me to put the names of the custom toolbars
added to a list in the spreadsheet, and have the macro remove it when
it's hidden by deselecting the worksheet. Then make sure only those
that are shown are cleared when the Workbook is closed. I'll have to
make sure there's no problem if it closes a toolbar that's already been
closed by the user.


Also, in my experience, simply hiding the toolbar does not remove it from
Excel considering it a part of all toolbars available to the user.
(View-Toolbars). If a user opts to go to this location the toolbar will be
displayed but when selecting options from the toolbar either errors will be
created or the workbook to which the toolbars are associated will be opened
and the actions performed. I can't remember which of those two occur.

Therefore I have found that it is necessary to delete the toolbar from excel
when closing the applicable workbook, not just hiding it.

There is reference to this in a thread that davegb was working on before.

"Peter T" wrote:

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.






Thanks for all your replies. I'm working on 2 parallel solutions to
this problem. I started, with your help, GB, in another thread, with
creating and deleting the toolbars entirely, but that was getting very
complicated and was obviously not going to be done quickly or easily. I
decided to start by getting the toolbars just to Show and Hide as
needed. I realize this approach would leave open the possibility that
an end-user could access those hidden toolbars and try to use them. I
tested this out, and found that all they do is return the standard VBA
error message, and since I have the code protected, their only option
is "Cancel", which returns them to the spreadsheet, no damage done.
However, considering the Excel knowledge level of the vast majority of
my users, this in highly unlikely. But I still like the other solution
better, and will continue working on it. Meanwhile, if I get the call
that they need the next iteration of the workbook, I can give them this
one.
For now, my solution, which works, is as follows:
In "This Workbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideCustomTbars
End Sub

In a standard module called "Toolbars", I have:

Sub HideCustomTbars()
Dim cTBar As CommandBar

For Each cTBar In Application.CommandBars
If cTBar.Type = msoBarTypeNormal Then
If Not cTBar.Name = "Standard" Then
If Not cTBar.Name = "Formatting" Then
cTBar.Visible = False
End If
End If
End If
Next cTBar
End Sub

Does anyone see any potential pitfalls, other than those previously
noted, in this solution?

I will continue, as time permits, with the other, more elegant and
safer solution.

Another question. How does one get the AppEvents to capture events? Is this
the same as like trying to capture say that a user tried to copy something?
Are you able to override Excel events using that procedure?

Was trying to figure out if a reference had to be added, or if there was
additional code necessary to access the AppEvents class

"davegb" wrote:


GB wrote:
Not sure how well the following would work. I would recommend, since one
never knows what toolbars a user has gone through the effort of getting on
their screen. (Hopefully not ones that would defeat your code.) that you
either somehow in memory maintain a list of the toolbars that you have
created, like in a CommandBars object, to which you add your toolbars when
you create them for normal usage, or like some of the other code where you
have a "list" of set toolbar names. This way you just iterate on your known
toolbars (i.e. the real custom ones.) and hide only those toolbars that you
as the programmer have created. Not to interfere with other programming and
user options.

On the other hand if you need a toolbar to become visible to work, I
wouldn't see a problem with being intrusive and forcing it to appear. :)

"davegb" wrote:


GB wrote:

It wouldn't be difficult for me to put the names of the custom toolbars
added to a list in the spreadsheet, and have the macro remove it when
it's hidden by deselecting the worksheet. Then make sure only those
that are shown are cleared when the Workbook is closed. I'll have to
make sure there's no problem if it closes a toolbar that's already been
closed by the user.


Also, in my experience, simply hiding the toolbar does not remove it from
Excel considering it a part of all toolbars available to the user.
(View-Toolbars). If a user opts to go to this location the toolbar will be
displayed but when selecting options from the toolbar either errors will be
created or the workbook to which the toolbars are associated will be opened
and the actions performed. I can't remember which of those two occur.

Therefore I have found that it is necessary to delete the toolbar from excel
when closing the applicable workbook, not just hiding it.

There is reference to this in a thread that davegb was working on before.

"Peter T" wrote:

I can't see anything obvious in your description of changes as to why it
doesn't work, I assume first you set an instance of the class running in one
of these ways -

'top of normal module
Public AppObject as Class1

' in a routine
Set AppObject = New Class1

or
Public AppObject as New Class1
' behind the scenes, each time you refer to AppObject checks an instance
exists, if not create a New one and assigns to AppObject

("Class1" being the whatever name of the class module)

Re the dropdowns - I meant the two at the top of the class code module. In
the left of the two dropdowns you should see the name of your WithEvents
variable "AppEvents". Select that, then in the right dropdown you should see
the events you can use. Best to select them here as you can be sure they'll
be written correctly.

FWIW might be worth renaming AppObject to (say) WBobject if you now intend
to trap workbook level events.

Regards,
Peter T

"davegb" wrote in message
ups.com...
Thanks for your reply. I'm trying to get the custom toolbars called
when each sheet in the workbook is selected, to hide when the workbook
is closed, so I guess it's a document level macro.

Peter T wrote:
Private Sub appevents_beforeclose()

This event traps workbook level events
Public WithEvents AppEvents As Workbook


I changed "Private Sub appevents_beforeclose()" to "Public WithEvents
AppEvents As Workbook" in the class module "HideToolbar".


and in the normal module change
Set AppObject.AppEvents = Application
to
Set AppObject.AppEvents = workbooks("myBook.xls")
or = some ref to the particular workbook.

I changed "Set AppObject.AppEvents = Application" to "Set
AppObject.AppEvents = workbooks("PIP DD Template.xls")" in the regular
module.
But it still doesn't work. Any other ideas?


But if you want to trap events at application level

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel As Boolean)
If Wb.Name = "MyBook.xls" Then

Application.CommandBars("Abuse").Visible = False
End If

End Sub

Tip - instead of writing or pasting first & last event lines, select
your
withevents var' name in the top middle dropdown, then find your event in
the
top right dropdown.

I'm not following you here. Do you mean left and right dropdowns? The
left one is toward the middle of the VBE, is that what you mean by "top
middle"?
Can you elaborate on this? I'm not sure what you mean by "select
withevents var' name".


Regards,
Peter T

"davegb" wrote in message
oups.com...
I want to hide a toolbar called "Abuse" when the workbook closes. I
looked it up in Walkenbach and tried to modify the code I found there
for my purpose. It doesn't work, but it doesn't hang either.
Surprising
that it doesn't hang up.
Here is the code from the Class Module "HideToolbar"

Public WithEvents AppEvents As Application

Private Sub appevents_beforeclose()
Application.CommandBars("Abuse").Visible = False
End Sub

Here is the code from "ThisWorkbook":

Private Sub BeforeClose()
Call Init
End Sub

Here's the code from the regular module:

Dim AppObject As New HideToolbar
Sub Init()
Set AppObject.AppEvents = Application
End Sub

Any ideas on why it doesn't hide the toolbar?
Thanks in advance.






Thanks for all your replies. I'm working on 2 parallel solutions to
this problem. I started, with your help, GB, in another thread, with
creating and deleting the toolbars entirely, but that was getting very
complicated and was obviously not going to be done quickly or easily. I
decided to start by getting the toolbars just to Show and Hide as
needed. I realize this approach would leave open the possibility that
an end-user could access those hidden toolbars and try to use them. I
tested this out, and found that all they do is return the standard VBA
error message, and since I have the code protected, their only option
is "Cancel", which returns them to the spreadsheet, no damage done.
However, considering the Excel knowledge level of the vast majority of
my users, this in highly unlikely. But I still like the other solution
better, and will continue working on it. Meanwhile, if I get the call
that they need the next iteration of the workbook, I can give them this
one.
For now, my solution, which works, is as follows:
In "This Workbook" module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideCustomTbars
End Sub

In a standard module called "Toolbars", I have:

Sub HideCustomTbars()
Dim cTBar As CommandBar

For Each cTBar In Application.CommandBars
If cTBar.Type = msoBarTypeNormal Then
If Not cTBar.Name = "Standard" Then
If Not cTBar.Name = "Formatting" Then
cTBar.Visible = False
End If
End If
End If
Next cTBar
End Sub

Does anyone see any potential pitfalls, other than those previously
noted, in this solution?

I will continue, as time permits, with the other, more elegant and
safer solution.