Unable to disable macro

[Lisa]

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

 

[Carim]

Hi,

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

HTH

 

[Carim]

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

 

[Carim]

....
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

 

[Lisa]

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]

Hi Lisa,

Have a go with :

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

HTH

 

[Lisa]

Hi

Still no joy on this

 

[Carim]

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

 

[Lisa]

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

 

[Lisa]

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

 

[Carim]

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/micr...&lnk=gst&q=restore+cell+menu#4c110ddeb0d1a906

HTH

 

[Carim]

Lisa,

Glad you managed to fix your problem ...

Thanks for the feedback