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

[GrilledCheese]

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!

 

[General Ledger]

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

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]

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>

Sub Menu_Disable()    CommandBars("Worksheet menu bar").Controls("New &Menu").Enabled = False End Sub
</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.

 

[GTO]

What version of Excel are you running?

 

[GrilledCheese]

Microsoft Excel 2010

What version of Excel are you running?

 

[GrilledCheese]

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]

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

 

[Teeroy]

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!

 

[GrilledCheese]

All advice are useful. I decided to use Protect Workbook for ease. Thanks again!!

 

[mrmmickle1]

What about using an event to trigger a msgbox and remove a newly added sheet like this:

[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