VBA, Disable/Prevent Delete Sheet in Excel 2013/2016 without protecting workbook, Help!

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
Hi All,

I want to disable/prevent user to delete sheet (both through Home > Cells > Delete Sheet, or Right click on sheet > Delete), but I can't use protect workbook for this one. so VBA code is the only way, I've search anywhere but it fails because the code for excel 2007.

k0kb3r.jpg
[/IMG]


2hywhfk.jpg
[/IMG]

Please help,
thanks a lot
warm regards

Adrian
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,732
Office Version
  1. 2016
Platform
  1. Windows
Even if you find a code that does what you want, the users can still delete the sheets if Macros are disabled

What's the reason you can't protect the workbook structure ?
 
Last edited:

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
Even if you find a code that does what you want, the users can still delete the sheets if Macros are disabled

What's the reason you can't protect the workbook structure ?


Hi Jaafar,

thanks for the reply, the user will still need to enable the macro, to be able to fill data within it.
because it's crashed with other macro, that's why protect workbook is not an option.

thanks a lot
warm regards

Adrian
 

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
Hi Jaafar,

thanks for the reply, the user will still need to enable the macro, to be able to fill data within it.
because it's crashed with other macro, that's why protect workbook is not an option.

thanks a lot
warm regards

Adrian

You did not answered to Jafar what is preventing you to protect the structure of the workbook ? (protecting structure is different than sheet protected by password)
 
Last edited:

radian89

Board Regular
Joined
Nov 12, 2015
Messages
112
You did not answered to Jafar what is preventing you to protect the structure of the workbook ? (protecting structure is different than sheet protected by password)

Hi Netuser,

I'm quite sure to answer that question, I can't protect workbook (not worksheet) because it'll crashed with other macro
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,732
Office Version
  1. 2016
Platform
  1. Windows
You can customize the Ribbon and disable the delete-sheet controls
A quick web search came up with this

Alternatively, I can post some API based workaround that uses a hook but although it works and compile as well runtime errors are taken care of, it can still crash the application if the vbe is accidently reset before the hook is released peopperly
 
Last edited:

Forum statistics

Threads
1,171,699
Messages
5,876,995
Members
433,227
Latest member
Vincent518

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