I am using workbook_open to hide commandbars. I am using workbook_beforeclose
to reset them.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Enabled = True
Application.CommandBars("Toolbar List").Enabled = True
End Sub

The problem is that when I choose to 'Cancel' out of closing the workbook,
the reset happens anyway and all of the command bars are visible. Pleas
advise on a solution.

cm

I use application level events

so I have an xla that instantiates a class module where I have a variable
decalred WITHEVENTS as an excel appliction. The class has workbook close etc
events and I use this to control which command bars get shown or hidden

you can customise it for specific workbooks to hide all menu/command bars

"cm" wrote:

I am using workbook_open to hide commandbars. I am using workbook_beforeclose
to reset them.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Enabled = True
Application.CommandBars("Toolbar List").Enabled = True
End Sub

The problem is that when I choose to 'Cancel' out of closing the workbook,
the reset happens anyway and all of the command bars are visible. Pleas
advise on a solution.

cm

I'm sorry that is a bit beyond my current expertise level. I am pretty new to
vb. Is there anything a little less complex that you could suggest?

cm



"Patrick Molloy" wrote:

I use application level events

so I have an xla that instantiates a class module where I have a variable
decalred WITHEVENTS as an excel appliction. The class has workbook close etc
events and I use this to control which command bars get shown or hidden

you can customise it for specific workbooks to hide all menu/command bars

"cm" wrote:

I am using workbook_open to hide commandbars. I am using workbook_beforeclose
to reset them.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Enabled = True
Application.CommandBars("Toolbar List").Enabled = True
End Sub

The problem is that when I choose to 'Cancel' out of closing the workbook,
the reset happens anyway and all of the command bars are visible. Pleas
advise on a solution.

cm

I found a way in another post:

For saving and closing a workbook the best way is to use the
SaveChanges parameter of the Workbook.Close method:

ActiveWorkbook.Close SaveChanges:=True

You can also prevent certain prompts by using
Application.DisplayAlerts, e.g.

Application.DisplayAlerts = False

thanks for your help

"Patrick Molloy" wrote:

I use application level events

so I have an xla that instantiates a class module where I have a variable
decalred WITHEVENTS as an excel appliction. The class has workbook close etc
events and I use this to control which command bars get shown or hidden

you can customise it for specific workbooks to hide all menu/command bars

"cm" wrote:

I am using workbook_open to hide commandbars. I am using workbook_beforeclose
to reset them.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Enabled = True
Application.CommandBars("Toolbar List").Enabled = True
End Sub

The problem is that when I choose to 'Cancel' out of closing the workbook,
the reset happens anyway and all of the command bars are visible. Pleas
advise on a solution.

cm