Thread: Restoring ToolBars/Commandbars

Good afternoon,

After reading through a number of questions on the newsgroup re.
hiding/restoring toolbars and commandbars. I chose the code below to use in
my VBA project. The code works find except when a user click on the x button
in the top right hand of the workbook. In this instance the workbook closes
and the commandbars/toolbars are NOT restored. Does someone know a work
around to this problem?? All ideas much appreciated.

Option Explicit


Private mFormulaBar

Private Sub Workbook_Activate()
Dim oCB As CommandBar


'Remove commandbars
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next


'RemoveFormulaBar
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub




Private Sub Workbook_Deactivate()

Dim oCB As CommandBar


'Restore commandbars
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next


'RestoreFormulaBar
Application.DisplayFormulaBar = mFormulaBar
End Sub

Add the deactivate code to the BeforeClose event as well.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"CiaraG" <CiaraG@discussions.microsoft.com> wrote in message
news:0B969109-E37F-414D-A09F-B584C9EFF8A7@microsoft.com...
> Good afternoon,
>
> After reading through a number of questions on the newsgroup re.
> hiding/restoring toolbars and commandbars. I chose the code below to use
in
> my VBA project. The code works find except when a user click on the x
button
> in the top right hand of the workbook. In this instance the workbook
closes
> and the commandbars/toolbars are NOT restored. Does someone know a work
> around to this problem?? All ideas much appreciated.
>
> Option Explicit
>
>
> Private mFormulaBar
>
> Private Sub Workbook_Activate()
> Dim oCB As CommandBar
>
>
> 'Remove commandbars
> For Each oCB In Application.CommandBars
> oCB.Enabled = False
> Next
>
>
> 'RemoveFormulaBar
> mFormulaBar = Application.DisplayFormulaBar
> Application.DisplayFormulaBar = False
> End Sub
>
>
>
>
> Private Sub Workbook_Deactivate()
>
> Dim oCB As CommandBar
>
>
> 'Restore commandbars
> For Each oCB In Application.CommandBars
> oCB.Enabled = True
> Next
>
>
> 'RestoreFormulaBar
> Application.DisplayFormulaBar = mFormulaBar
> End Sub

maybe somewhere in your code
you set application.enableevents=false ?

then the workbook_deactivate event will not fire.

I dont think it's necessary to do as Bob Says
(to repeat the code for beforeclose event)

although the beforeclose event fires BEFORE
the deactivate event, the deactiveate will fire.
(that is if enableevents = true ;-)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CiaraG wrote :

> Good afternoon,
>
> After reading through a number of questions on the newsgroup re.
> hiding/restoring toolbars and commandbars. I chose the code below to
> use in my VBA project. The code works find except when a user click
> on the x button in the top right hand of the workbook. In this
> instance the workbook closes and the commandbars/toolbars are NOT
> restored. Does someone know a work around to this problem?? All
> ideas much appreciated.
>
> Option Explicit
>
>
> Private mFormulaBar
>
> Private Sub Workbook_Activate()
> Dim oCB As CommandBar
>
>
> 'Remove commandbars
> For Each oCB In Application.CommandBars
> oCB.Enabled = False
> Next
>
>
> 'RemoveFormulaBar
> mFormulaBar = Application.DisplayFormulaBar
> Application.DisplayFormulaBar = False
> End Sub
>
>
>
>
> Private Sub Workbook_Deactivate()
>
> Dim oCB As CommandBar
>
>
> 'Restore commandbars
> For Each oCB In Application.CommandBars
> oCB.Enabled = True
> Next
>
>
> 'RestoreFormulaBar
> Application.DisplayFormulaBar = mFormulaBar
> End Sub

Thanks folks, I had the de-activate code in the close workbook event but it
doesn't appear to be working.

I don't have application.enableevents anywhere in my project. It might have
soemthing to do with the code that I have in my beforeclose event - any
ideas?? See below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Worksheets("WelcomeScreen").Select

Call ClearTimesheet
Sheets("Template").Visible = False
Sheets("JobCodes").Visible = False
Sheets("EmployeeCodes").Visible = False
Sheets("EmployeeData").Visible = False

ActiveWindow.DisplayWorkbookTabs = True

ThisWorkbook.Saved = True
ActiveWorkbook.Close

End Sub


"keepITcool" wrote:

>
> maybe somewhere in your code
> you set application.enableevents=false ?
>
> then the workbook_deactivate event will not fire.
>
> I dont think it's necessary to do as Bob Says
> (to repeat the code for beforeclose event)
>
> although the beforeclose event fires BEFORE
> the deactivate event, the deactiveate will fire.
> (that is if enableevents = true ;-)
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> CiaraG wrote :
>
> > Good afternoon,
> >
> > After reading through a number of questions on the newsgroup re.
> > hiding/restoring toolbars and commandbars. I chose the code below to
> > use in my VBA project. The code works find except when a user click
> > on the x button in the top right hand of the workbook. In this
> > instance the workbook closes and the commandbars/toolbars are NOT
> > restored. Does someone know a work around to this problem?? All
> > ideas much appreciated.
> >
> > Option Explicit
> >
> >
> > Private mFormulaBar
> >
> > Private Sub Workbook_Activate()
> > Dim oCB As CommandBar
> >
> >
> > 'Remove commandbars
> > For Each oCB In Application.CommandBars
> > oCB.Enabled = False
> > Next
> >
> >
> > 'RemoveFormulaBar
> > mFormulaBar = Application.DisplayFormulaBar
> > Application.DisplayFormulaBar = False
> > End Sub
> >
> >
> >
> >
> > Private Sub Workbook_Deactivate()
> >
> > Dim oCB As CommandBar
> >
> >
> > 'Restore commandbars
> > For Each oCB In Application.CommandBars
> > oCB.Enabled = True
> > Next
> >
> >
> > 'RestoreFormulaBar
> > Application.DisplayFormulaBar = mFormulaBar
> > End Sub
>

further are you SURE your code is valid?
you use activeworkbook and thisworkbook

If THISworkbook is closed you ASSUME
the ACTIVEworkbook has sheets("template,employee codes etc)
then you toggle visiblilty of the activeworkbook sheets
and close it.

If THISworkbook is not the ACTIVEworkbook
when excel closes... what should happen?

maybe better to use
with thisworkbook
.windows(1).displayworkbooktabs=true
.sheets("x").visible=true
.close
end with

not sure of your code's intentions

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CiaraG wrote :

> Thanks folks, I had the de-activate code in the close workbook event
> but it doesn't appear to be working.
>
> I don't have application.enableevents anywhere in my project. It
> might have soemthing to do with the code that I have in my
> beforeclose event - any ideas?? See below:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Worksheets("WelcomeScreen").Select
>
> Call ClearTimesheet
> Sheets("Template").Visible = False
> Sheets("JobCodes").Visible = False
> Sheets("EmployeeCodes").Visible = False
> Sheets("EmployeeData").Visible = False
>
> ActiveWindow.DisplayWorkbookTabs = True
>
> ThisWorkbook.Saved = True
> ActiveWorkbook.Close
>
> End Sub
>
>
> "keepITcool" wrote:
>
> >
> > maybe somewhere in your code
> > you set application.enableevents=false ?
> >
> > then the workbook_deactivate event will not fire.
> >
> > I dont think it's necessary to do as Bob Says
> > (to repeat the code for beforeclose event)
> >
> > although the beforeclose event fires BEFORE
> > the deactivate event, the deactiveate will fire.
> > (that is if enableevents = true ;-)
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > CiaraG wrote :
> >
> > > Good afternoon,
> > >
> > > After reading through a number of questions on the newsgroup re.
> > > hiding/restoring toolbars and commandbars. I chose the code
> > > below to use in my VBA project. The code works find except when
> > > a user click on the x button in the top right hand of the
> > > workbook. In this instance the workbook closes and the
> > > commandbars/toolbars are NOT restored. Does someone know a work
> > > around to this problem?? All ideas much appreciated.
> > >
> > > Option Explicit
> > >
> > >
> > > Private mFormulaBar
> > >
> > > Private Sub Workbook_Activate()
> > > Dim oCB As CommandBar
> > >
> > >
> > > 'Remove commandbars
> > > For Each oCB In Application.CommandBars
> > > oCB.Enabled = False
> > > Next
> > >
> > >
> > > 'RemoveFormulaBar
> > > mFormulaBar = Application.DisplayFormulaBar
> > > Application.DisplayFormulaBar = False
> > > End Sub
> > >
> > >
> > >
> > >
> > > Private Sub Workbook_Deactivate()
> > >
> > > Dim oCB As CommandBar
> > >
> > >
> > > 'Restore commandbars
> > > For Each oCB In Application.CommandBars
> > > oCB.Enabled = True
> > > Next
> > >
> > >
> > > 'RestoreFormulaBar
> > > Application.DisplayFormulaBar = mFormulaBar
> > > End Sub
> >

further are you SURE your code is valid?
you use activeworkbook and thisworkbook

If THISworkbook is closed you ASSUME
the ACTIVEworkbook has sheets("template,employee codes etc)
then you toggle visiblilty of the activeworkbook sheets
and close it.

If THISworkbook is not the ACTIVEworkbook
when excel closes... what should happen?

maybe better to use
with thisworkbook
.windows(1).displayworkbooktabs=true
.sheets("x").visible=true
.close
end with

not sure of your code's intentions

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CiaraG wrote :

> Thanks folks, I had the de-activate code in the close workbook event
> but it doesn't appear to be working.
>
> I don't have application.enableevents anywhere in my project. It
> might have soemthing to do with the code that I have in my
> beforeclose event - any ideas?? See below:
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Worksheets("WelcomeScreen").Select
>
> Call ClearTimesheet
> Sheets("Template").Visible = False
> Sheets("JobCodes").Visible = False
> Sheets("EmployeeCodes").Visible = False
> Sheets("EmployeeData").Visible = False
>
> ActiveWindow.DisplayWorkbookTabs = True
>
> ThisWorkbook.Saved = True
> ActiveWorkbook.Close
>
> End Sub
>
>
> "keepITcool" wrote:
>
> >
> > maybe somewhere in your code
> > you set application.enableevents=false ?
> >
> > then the workbook_deactivate event will not fire.
> >
> > I dont think it's necessary to do as Bob Says
> > (to repeat the code for beforeclose event)
> >
> > although the beforeclose event fires BEFORE
> > the deactivate event, the deactiveate will fire.
> > (that is if enableevents = true ;-)
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > CiaraG wrote :
> >
> > > Good afternoon,
> > >
> > > After reading through a number of questions on the newsgroup re.
> > > hiding/restoring toolbars and commandbars. I chose the code
> > > below to use in my VBA project. The code works find except when
> > > a user click on the x button in the top right hand of the
> > > workbook. In this instance the workbook closes and the
> > > commandbars/toolbars are NOT restored. Does someone know a work
> > > around to this problem?? All ideas much appreciated.
> > >
> > > Option Explicit
> > >
> > >
> > > Private mFormulaBar
> > >
> > > Private Sub Workbook_Activate()
> > > Dim oCB As CommandBar
> > >
> > >
> > > 'Remove commandbars
> > > For Each oCB In Application.CommandBars
> > > oCB.Enabled = False
> > > Next
> > >
> > >
> > > 'RemoveFormulaBar
> > > mFormulaBar = Application.DisplayFormulaBar
> > > Application.DisplayFormulaBar = False
> > > End Sub
> > >
> > >
> > >
> > >
> > > Private Sub Workbook_Deactivate()
> > >
> > > Dim oCB As CommandBar
> > >
> > >
> > > 'Restore commandbars
> > > For Each oCB In Application.CommandBars
> > > oCB.Enabled = True
> > > Next
> > >
> > >
> > > 'RestoreFormulaBar
> > > Application.DisplayFormulaBar = mFormulaBar
> > > End Sub
> >

Hi,

Thanks for all your help so far. I am a novice when it comes to VBA (as you
may have noticed). What I would like my VBA project to do ultimately is

(1) Prevent the user from closing out of the excel workbook by clicking on
the "X" button on the top right hand corner of the screen;
(2) By doing the above this will force the user to follow instruction and
close out using the Exit Button on a user form.
(3) When the user exits the user form that it will do the following:
(a) Hide a number of worksheets (so if the user disables the macros when
he opens the workbook that these are hidden)
(b) Save the workbook
(c) Restore all commandbuttons and toolbars.

This is what my code was successfully doing in the beforeclose event. It
just doesn't work whenever the user clicks on the X button of the workbook.
Finding a solution to the X Button problem would be great.

Starting to loose my mind!!!

Thanks,

Ciara





"keepITcool" wrote:

>
> further are you SURE your code is valid?
> you use activeworkbook and thisworkbook
>
> If THISworkbook is closed you ASSUME
> the ACTIVEworkbook has sheets("template,employee codes etc)
> then you toggle visiblilty of the activeworkbook sheets
> and close it.
>
> If THISworkbook is not the ACTIVEworkbook
> when excel closes... what should happen?
>
> maybe better to use
> with thisworkbook
> .windows(1).displayworkbooktabs=true
> .sheets("x").visible=true
> .close
> end with
>
> not sure of your code's intentions
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> CiaraG wrote :
>
> > Thanks folks, I had the de-activate code in the close workbook event
> > but it doesn't appear to be working.
> >
> > I don't have application.enableevents anywhere in my project. It
> > might have soemthing to do with the code that I have in my
> > beforeclose event - any ideas?? See below:
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >
> > Worksheets("WelcomeScreen").Select
> >
> > Call ClearTimesheet
> > Sheets("Template").Visible = False
> > Sheets("JobCodes").Visible = False
> > Sheets("EmployeeCodes").Visible = False
> > Sheets("EmployeeData").Visible = False
> >
> > ActiveWindow.DisplayWorkbookTabs = True
> >
> > ThisWorkbook.Saved = True
> > ActiveWorkbook.Close
> >
> > End Sub
> >
> >
> > "keepITcool" wrote:
> >
> > >
> > > maybe somewhere in your code
> > > you set application.enableevents=false ?
> > >
> > > then the workbook_deactivate event will not fire.
> > >
> > > I dont think it's necessary to do as Bob Says
> > > (to repeat the code for beforeclose event)
> > >
> > > although the beforeclose event fires BEFORE
> > > the deactivate event, the deactiveate will fire.
> > > (that is if enableevents = true ;-)
> > >
> > >
> > > --
> > > keepITcool
> > > > www.XLsupport.com | keepITcool chello nl | amsterdam
> > >
> > >
> > > CiaraG wrote :
> > >
> > > > Good afternoon,
> > > >
> > > > After reading through a number of questions on the newsgroup re.
> > > > hiding/restoring toolbars and commandbars. I chose the code
> > > > below to use in my VBA project. The code works find except when
> > > > a user click on the x button in the top right hand of the
> > > > workbook. In this instance the workbook closes and the
> > > > commandbars/toolbars are NOT restored. Does someone know a work
> > > > around to this problem?? All ideas much appreciated.
> > > >
> > > > Option Explicit
> > > >
> > > >
> > > > Private mFormulaBar
> > > >
> > > > Private Sub Workbook_Activate()
> > > > Dim oCB As CommandBar
> > > >
> > > >
> > > > 'Remove commandbars
> > > > For Each oCB In Application.CommandBars
> > > > oCB.Enabled = False
> > > > Next
> > > >
> > > >
> > > > 'RemoveFormulaBar
> > > > mFormulaBar = Application.DisplayFormulaBar
> > > > Application.DisplayFormulaBar = False
> > > > End Sub
> > > >
> > > >
> > > >
> > > >
> > > > Private Sub Workbook_Deactivate()
> > > >
> > > > Dim oCB As CommandBar
> > > >
> > > >
> > > > 'Restore commandbars
> > > > For Each oCB In Application.CommandBars
> > > > oCB.Enabled = True
> > > > Next
> > > >
> > > >
> > > > 'RestoreFormulaBar
> > > > Application.DisplayFormulaBar = mFormulaBar
> > > > End Sub
> > >
>

This sounds like you you want to "take over" Excel so only your toolbar/menus
are used while the project is open. If so then...

You need to store a list of all the commandbars you want to hide, then
iterate the list to restore them before close. If you could just do the
visible bars then that's a simple task. If you need to restrict user access
to other commandbars then you need to "disable" the various paths one could
take to get at them. This is probably better than having to iterate the
commandbars collection for every bar, which could take fairly long. If
something goes wrong in the process, ..then what?

Your code sample disables all of them, which suggests your project provides
all the menus, popups, etc that it uses. That's admirable, but it has its
challenges in making it all happen smoothly, and without any hitches.

In any case, you need a workspace management strategy for handling this,
-something like 'StoreSettings()' for starting up and 'RestoreSettings()' for
shutting down. Just call them appropriately from code in "ThisWorkbook". This
will, at the very least, give you a start for what changes you make and how
they need to be restored.

If the settings are only for the current session, I suggest using a hidden
sheet to store the info would be the easiest way. A simple format would be to
list the settings you want to manipulate in column1 of a 3-column range. In
the other two, use one for existing settings and the other for your settings.
Then write code to step through the list on startup, that collects and writes
the values. Then go through it to read and apply your values. This leaves one
more iteration on close to read and restore the original settings.

Have another area(s) on the sheet to store the commandbar names, and their
respective values. I would put them in separate lists according to the
setting. (.Visible list, .Enabled list...) That way you can process them more
easily in your code, ..if not more organized. This could be a single cell for
each list, containing the names separated by commas, named BarsVisible,
BarsEnabled, and so on. (just a suggestion using the setting in the name)

If you need some good reference material for this, here's some books that
are worth more than their weight in gold:

Excel xxxx Power Programming with VBA by John Walkenbach is a good one to
start with.

Excel xxxx VBA: Programmer's Reference by Stephen Bullen, Rob Bovey, John
Green, et al (If you still aren't full)
and by the same authors...
Professional Excel Development (if you're really looking to "get into it")

Amazon.com has them all. Good luck!
GS