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

 

[Smitty]

Try re-enabling it in the Before Close event.

 

[Aaragorn]

Try re-enabling it in the Before Close event.

Can you Please direct me how to do that?

 

[Aaragorn]

I tried this and it didn't work. did I miss something?

Private Sub Workbook_BeforeClose()
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

 

[Aaragorn]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

ok, it still isn't working. The delete function remains disabled in other workbooks after I close this workbook.

darn.

 

[Aaragorn]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

This is what the help text says about it...

BeforeClose Event
Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)


Cancel False when the event occurs. If the event procedure sets this argument to True, the close operation stops and the workbook is left open.



___________________



so I wonder if cancel is false when the event occurs what will trigger the sub to run on close?


Private Sub Workbook_BeforeClose(false)

If the return value were true then it would run the sub right? but if the value is true then they are canceling the close and that's backwards too. I want the sub to run when we close the workbook so cancel would be false but that doesn't seem to be making the sub execute.

I'm confused. How do I get this sub to execute on close?

 

[Smitty]

The cancel argument is so you can evaluate whether or not the wb should be closed, and it's optional. For instance you want to see if a user has filled out required information. If they haven't you'd set Cancel = True to return to Excel.

In your case you don't need to mess with it and for re-enabling the command bar what Peter posted should work fine.

 

[Aaragorn]

The cancel argument is so you can evaluate whether or not the wb should be closed, and it's optional. For instance you want to see if a user has filled out required information. If they haven't you'd set Cancel = True to return to Excel.

In your case you don't need to mess with it and for re-enabling the command bar what Peter posted should work fine.

ok, well all I know is I copied and pasted it and tried it and it didn't work. So I've got no idea why it didn't. I was guessing that it didn't execute the sub or something.

I really don't know why it didn't work.

this is exactly what I pasted and tried...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars.FindControl(ID:=292).Enabled = True
End Sub

 

[Smitty]

Try this:

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