VBA Code to Prevent Users from Deleting any Worksheet in the Workbook

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
There are many articles and posts on how to prevent users from deleting worksheets in Excel but none that shows what to do. I do not want to use the Protect Workbook. I want to use VBA to hide the "Delete" button when you right click on the sheet tab. Thank you for your expertise!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

General Ledger

Active Member
Joined
Dec 31, 2007
Messages
460
[h=4]GrilledCheese,

Using VBA, you can disable menus and commands. Check out How to customize menus and menu bars in Excel


Disable a menu control on a command bar
[/h]A menu control that is disabled appears dimmed and is not available on a command bar. The following example disables the New Menu menu:

<code>
Code:
Sub Menu_Disable()    CommandBars("Worksheet menu bar").Controls("New &Menu").Enabled = False End Sub
</pre></code>[/FONT]<code></code>[FONT=arial]<code>
<code>

Best of luck,

G/L</pre></code></pre></code>
<code></code>


 

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
GrilledCheese,

Using VBA, you can disable menus and commands. Check out How to customize menus and menu bars in Excel


Disable a menu control on a command bar


A menu control that is disabled appears dimmed and is not available on a command bar. The following example disables the New Menu menu:

<code>
Code:
Sub Menu_Disable()    CommandBars("Worksheet menu bar").Controls("New &Menu").Enabled = False End Sub
</code>
Code:
<code></code>[FONT=arial]<code>
<code>
</code></code>[/FONT]<code><code>


Best of luck,

G/L
</code></code><code></code>
This option only works prior to excel 2007; the way to access the ribbon is different (and in my opinion much more tedious). It also won't affect contextual option menus such as right click on the sheet Tab.

I don't know why you don't want to use protection, protecting the workbook for structure is the easiest way to achieve the protection against sheet deletion.
 

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
The reason why I don't want to use Protect Workbook is because I still want users to be able to insert sheets, hide sheets, unhide sheets, and move sheets.
This option only works prior to excel 2007; the way to access the ribbon is different (and in my opinion much more tedious). It also won't affect contextual option menus such as right click on the sheet Tab.

I don't know why you don't want to use protection, protecting the workbook for structure is the easiest way to achieve the protection against sheet deletion.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

See the first answer in this thread (by Andrei Smolin).

The XML coding is short and works nicely for the individual workbook :)

Please follow the links Mr. Smolin gives to download the UI editor if you wish to follow this course.

BTW Teeroy, I quite agree! The Ribbon still seems quite a pain to me. Not enough time to learn I suppose is the biggest irksome thing for me. Not to rant, but jeepers! Just when I had become sorta competent at controlling command bars and defusing the 57 million shortcut key combos, they had to bring out something that's supposed to be used for gift wrapping. Grrrrr....

Mark

Oopsie! I got so worked up about the Ribbon, I forgot to include the link...

How to disable Right Click Delete Sheet Command in Excel 2010 using VBA
 
Last edited:

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
The Ribbon still seems quite a pain to me. Not enough time to learn I suppose is the biggest irksome thing for me. Not to rant, but jeepers! Just when I had become sorta competent at controlling command bars and defusing the 57 million shortcut key combos, they had to bring out something that's supposed to be used for gift wrapping. Grrrrr....

Hi GTO, I haven't seen you on this Forum before. Great link, but I'm still not sure I'm going to try VBA customising the ribbon.
A P.I.T.A. gift wrapped is still a P.I.T.A. It's MS user consultation at its best; you'll get what we give you and like it!
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
What about using an event to trigger a msgbox and remove a newly added sheet like this:

Code:
[COLOR=#0000ff]Private Sub[/COLOR] Workbook_NewSheet([COLOR=#0000ff]ByVal[/COLOR] Sh [COLOR=#0000ff]As Object[/COLOR])

[COLOR=#008000]'Prevents users from adding worksheets[/COLOR]

 [COLOR=#0000ff]   With [/COLOR]Application
        .DisplayAlerts = [COLOR=#0000ff]False[/COLOR]
        .ScreenUpdating = [COLOR=#0000ff]False[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
    Sh.Delete
    [COLOR=#0000ff]With[/COLOR] Application
        .DisplayAlerts = [COLOR=#0000ff]True[/COLOR]
        .ScreenUpdating = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
    MsgBox "Adding sheets has been disabled.", vbCritical, "PCLS Master Review Log"
    
[COLOR=#0000ff]End Sub[/COLOR]

INSERT CODE IN WORKBOOK MODULE
 

Watch MrExcel Video

Forum statistics

Threads
1,122,745
Messages
5,597,887
Members
414,185
Latest member
tarun19954

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top