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

[radian89]

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.

[/IMG]

[/IMG]

Please help,
thanks a lot
warm regards

Adrian

 

[Jaafar Tribak]

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 ?

 

[radian89]

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]

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)

 

[radian89]

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]

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

 

[netuser]

Hi Netuser,

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

Maybe someone can adopt the code posted here to work with Excel > 2010 (if you have older version it will work)


http://www.mrexcel.com/forum/excel-...n-sheets-excel-visual-basic-applications.html