• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Disable Sheet Tab Delete

I use the following code below to disable the Sheet Rename when user right clicks on the sheet tab.

Is there a way to add the disabling of the Delete command too, so that when the true and false statements of the already used code in "EnableSheetRename" (code below) is used it disables both Rename and Delete?  I am not sure how to determine the Control or find the ID:= that relates to the Delete command there.

Please advise and thanks. -R-
Sub EnableSheetRename(blnEnable As Boolean)

'Toggles Sheet Tab Renaming
'EnableSheetRename true      to enable and:
'EnableSheetRename False     to disable.

   Dim ctl As CommandBarControl
   For Each ctl In Application.CommandBars.FindControls(ID:=889)
      ctl.Enabled = blnEnable
   Next ctl
End Sub

Open in new window

0
RWayneH
Asked:
RWayneH
  • 4
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
First of all, you can do this by protecting the workbook. To prevent worksheets from being added and deleted the workbook structure needs to be protected. Choose the menu command Tools->Protection->Protect Workbook and click OK.

The code below illustrates how to prevent worksheets from being deleted without the workbook structure being protected. The approach is to hide the menu commands that perform the worksheet deletions. There are two: one in the Edit menu and a second in the sheet tab context menu.

Include this code in the ThisWorkbook module:

Private Sub Workbook_Activate()

   Application.CommandBars("Edit").Controls("Delete Sheet").Visible = False
   Application.CommandBars("Ply").Controls("Delete").Visible = False

End Sub

Private Sub Workbook_Deactivate()

   Application.CommandBars("Edit").Controls("Delete Sheet").Visible = True
   Application.CommandBars("Ply").Controls("Delete").Visible = True

End Sub

Kevin
0
 
RWayneHAuthor Commented:
Thanks worked great... I just need to leave the sheet tabs in the wb, that were there, there and not allow any removal.  If the user inserts a sheet tab, that is fine... because everything newly added is programically deleted on wb close.

If the user needs that sheet?  They can use the move/copy, but I guess if they forget to check the "Copy" checkbox.... there is still a way to remove the sheet tab...

Unless you know of a way to make that checkbox stay checked and not let the user uncheck it.

-R-
0
 
zorvek (Kevin Jones)ConsultantCommented:
>Unless you know of a way to make that checkbox stay checked and not let the user uncheck it.

We're good, but not that good ;-)

Kevin
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RWayneHAuthor Commented:
Thanks, you answered the quesiton.  If I find out how to disable the checkbox in the Copy/Move I will post back to this.

Appreciate the help. -R-
0
 
zorvek (Kevin Jones)ConsultantCommented:
You misunderstood me ;-)

It can't be done. Don't even try.

Kevin
0
 
RWayneHAuthor Commented:
owwww the "can't" word.  You know what saying the "can't" word can create?  A mission!!  I understand that it may be very difficult to do, but impossible?  Will see.

Thanks -R-
0
 
zorvek (Kevin Jones)ConsultantCommented:
Ya, I know, the moment I clicked Submit I wondered if some yahoo out there was gonna think: "Oh, dude, zorvek is putting his reputation on the line again! Let me see if I can nail his ass!"

It will not have been the first time that's happened, nor the last.

Seriously though, it isn't possible. That part of the Excel system is not exposed to anyone.

Now, what you CAN do is intercept that command and execute a SendKeys to type "ALT+C" to toggle the check box on. A bit tricky with the timing but it can be done. However there is no way in hell you will be able to disable that box without some serious hacking of the Excel internals.

Kevin
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now