Help re-enabling the command bar on exit

[Aaragorn]

I have a worksheet that I protect from cell deletions by disabling the command function for 'delete' using the following code:


CommandBars.FindControl(ID:=292).Enabled = False


I execute this code in two subs:

Private Sub Worksheet_Change(ByVal Target As Range)
CommandBars.FindControl(ID:=292).Enabled = False

...
and
...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
CommandBars.FindControl(ID:=292).Enabled = False


...
all fine and good, works great only problem is...

once disabled this function is NOT re-enabled when I leave the workbook.

When the sheet deactivates I run this:
______________________________________________________________
Private Sub Worksheet_Deactivate()
CommandBars.FindControl(ID:=292).Enabled = True
End Sub
____________________________________________________

that works fine while I am changing worksheets within the workbook.

But it doesn't do for this function to remain disabled even after I close the workbook and then it's disabled in all the other workbooks.

What I need is to have the code re-enable this function when the workbook closes or when I activate a different workbook so that the function is only disabled within the specific worksheet of the specific workbook and it remains enabled everywhere else ESPECIALLY after I close that sheet and workbook where I have it disabled!

I tried this:

______________________________________________________________
Private Sub Workbook_Deactivate()
CommandBars.FindControl(ID:=292).Enabled = True
End Sub
____________________________________________________

it didn't work...

please help!!!!

 

[Aaragorn]

Try this:

Sub UnFubar()
  Dim x as CommandBar
    For each x in Application.CommandBars
       x.Enabled = True
    Next x
End Sub

ok, this isn't working either.

I tried this three ways.

1- as it's own sub on the worksheet I am closing and want to reenable as it closes.

2- like this as part of the before close sub..

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

and
3- as a stand alone in another workbook to run AFTER I closed the workbook I am protecting by disabling the delete to begin with.

In each instance the delete function remained disabled regardless. hum.

would the version I am running matter? ver. 2002

 

[Smitty]

I don't know what to tell you because that code should enable all command bars.

 

[Aaragorn]

I don't know what to tell you because that code should enable all command bars.

It makes no sense to me either. What can I do to debug this?

I know I can enable the delete function just not on the conditions I want to.

If I run this it enables...

Sub UnFubar()
Dim x As CommandBar
For Each x In Application.CommandBars
x.Enabled = True
Next x
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

or if I run this it enables....

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'CommandBars.FindControl(ID:=292).Enabled = True
'End Sub

but it doesn't seem to enable without this '292' line...

and it doesn't execute any of the things that has been suggested on this thread which it SHOULD execute fine.

what could be the cause?

 

[Aaragorn]

I did some debugging (such as I could think of)

This line works fine...
CommandBars.FindControl(ID:=292).Enabled = True

What is NOT working is the sub itself.
I know this because I added a message box to the sub it would tell me when it was running. The message box never opened from this code...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Hi, I am trying to re-enable the delete function.")
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

but it ALWAYS opens from this code....

'Private Sub Worksheet_Deactivate()
'MsgBox ("Hi, I am trying to re-enable the delete function.")
'CommandBars.FindControl(ID:=292).Enabled = True
'End Sub


so...


what is it about this sub that fails to execute? How can I find why it doesn't execute?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

 

[VoG]

At any point in any of your code are you turning off events using

Application.EnableEvents = False

If you are, you need to set it back to True at some point.

 

[Aaragorn]

At any point in any of your code are you turning off events using

Application.EnableEvents = False

If you are, you need to set it back to True at some point.

that's a good point. I do turn off events twice in the code and I just spent some time going over the code line by line to be sure that in both cases when it is turned off that it gets turned back on before exiting the subs that turn it off.

As near as I can tell it remains turned on at the end of both subs that turn it off.

I really thought you had the bug there, but after checking it appears that's not it.

What else could it be?