Unable to disable macro
 

Hi, I have created a macro in a worksheet, to disable the menu toolbars etc.
I had also protected the sheet (which I've since removed).

The *very* frustrating problem is, any other workbook I open now (either new
or existing) prevents me from rightclicking on the mouse to insert rows
columns etc.

I am presuming it is something to do with the macro I created, which looks
like as below, but I want to switch everything off and return back to normal
Excel operation when this workbook is closed. Can anyone point me in the
right direction - perhaps its an excel option somewhere I simply need to
switch off?

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

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

Unable to disable macro
 

Hi,

You shoud add the following instruction :
Cancel = True
in your workbook_beforeclose macro

HTH

Unable to disable macro
 

Hi Lisa,

In these instances, you must make sure you are reversing all your
initial instructions ...
It looks like you are missing :

Application.DisplayFormulaBar = True

HTH

Unable to disable macro
 

....
It could also that you need to replicate all your "back-to-normal"
instructions in a
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) macro

HTH

Unable to disable macro
 

Hi Carim

Thanks for getting back to me. I've added in your suggestions, but I still
cant use right click on the mouse for anything.

On you last suggestion, I added the Private Sub
Workbook_WindowDeactivate(ByVal Wn As Window) but this was only recognised
when I trimmed the statement to Private Sub Workbook_WindowDeactivate

"Carim" wrote:

....
It could also that you need to replicate all your "back-to-normal"
instructions in a
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) macro

HTH

Unable to disable macro
 

Hi Lisa,

Have a go with :

Sub Test()
Application.CommandBars("Cell").Enabled = True
End Sub

HTH

Unable to disable macro
 

Hi

Still no joy on this

"Carim" wrote:

Hi Lisa,

Have a go with :

Sub Test()
Application.CommandBars("Cell").Enabled = True
End Sub

HTH

Unable to disable macro
 

Lisa,

Obviously this incident was generated by another macro ..

Hope this one will fix your problem ...

Sub Test()
Application.CommandBars("Cell").Reset
End Sub

HTH

Unable to disable macro
 

Still no joy Grr lol!

I've made a discovery which means I can offer some further information.

When I go into the Visual Basic part, I have the following under 'This
Workbook'

Private Sub Workbook_Open()
'Application.DisplayFullScreen = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End Sub

Then under the Modules Section, I have 3 modules - 1 and 3 are empty (!) and
Module 2 contains the following (which is what I have been tweaking with your
suggestions)

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Cancel = True

Application.DisplayFormulaBar = mFormulaBar
Application.CommandBars("Cell").Enabled = True

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

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

I must admit, I'm not sure whether the primary code should reside under
'This Workbook' or within a module - surely I should consolidate these into
the correct place also?

Thanks

"Carim" wrote:

Lisa,

Obviously this incident was generated by another macro ..

Hope this one will fix your problem ...

Sub Test()
Application.CommandBars("Cell").Reset
End Sub

HTH

Unable to disable macro
 

Yippee!

Cracked it. I placed your suggestion of
Application.CommandBars("Cell").Enabled = True as the first line within the
Before_Close routing

Thanks for all of your help :-)

"Lisa" wrote:

Still no joy Grr lol!

I've made a discovery which means I can offer some further information.

When I go into the Visual Basic part, I have the following under 'This
Workbook'

Private Sub Workbook_Open()
'Application.DisplayFullScreen = True
Application.CommandBars("Worksheet Menu Bar").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End Sub

Then under the Modules Section, I have 3 modules - 1 and 3 are empty (!) and
Module 2 contains the following (which is what I have been tweaking with your
suggestions)

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Cancel = True

Application.DisplayFormulaBar = mFormulaBar
Application.CommandBars("Cell").Enabled = True

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

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

I must admit, I'm not sure whether the primary code should reside under
'This Workbook' or within a module - surely I should consolidate these into
the correct place also?

Thanks

"Carim" wrote:

Lisa,

Obviously this incident was generated by another macro ..

Hope this one will fix your problem ...

Sub Test()
Application.CommandBars("Cell").Reset
End Sub

HTH

Unable to disable macro
 

Lisa,

I am guessing all your problems are coming from your private
toolbar ..

Take a look at Ron's solution :
http://groups.google.com/group/micro...110ddeb0d1a906

HTH

Unable to disable macro
 

Lisa,

Glad you managed to fix your problem ...

Thanks for the feedback