asked on

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

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

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-

zorvek (Kevin Jones)

>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

RWayneH

ASKER

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-

zorvek (Kevin Jones)

You misunderstood me ;-)

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

Kevin

RWayneH

ASKER

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-

zorvek (Kevin Jones)

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