Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Prevent user from delete a worksheet in using the shortcut menu of

  Asked By: Molly    Date: Oct 25    Category: MS Office    Views: 648
  

I would like to hide the shortcut menu for the worksheet tabs when I
open a specific workbook.
I used the following codes in each worksheet.
Private Sub Worksheet_Activate()
On Error Resume Next
CommandBars("ply").Enabled = False
On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
CommandBars("ply").Enabled = True
On Error GoTo 0
End Sub

It works, but If I opened a new workbook the shortcut menu for the
worksheet tabs is still hidden.

I tried the following code, but the problem is the same.

Private Sub Workbook_Activate()
On Error Resume Next
CommandBars("ply").Enabled = False
On Error GoTo 0

End Sub

Private Sub Workbook_Close()
CommandBars("ply").Enabled = True
End Sub

Please could you help me to solve this problem?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Corinne Rogers     Answered On: Oct 25

Sounds like you need to test for the ActiveWorksheet to see whether you
should enable or disable the toolbar.

If ActiveSheet.Name = "ImportantWorksheet" then
Your code.

 
Answer #2    Answered By: Agatha Miller     Answered On: Oct 25

In order for you to detech when any of the opened  wks is activated,
you must create a application class event and put the code  in that
class' event handler....That way when you switch between the wk's the
code will still work.

 
Answer #3    Answered By: Sonya Flores     Answered On: Oct 25

I wrote the following codes
Private Sub Workbook_Close()
Application.CommandBars("ply").Enabled = True
End Sub
These code  should restore the worksheet  tab menu  when I close the workbook  and
the sheet tab should be present when I open a new workbook.

It still does not do it.

How do I restore the defaults settings in Excel?

 




Tagged:            

 
 
 
 

Related Post