Protect specific sheets from deletion

[jubi23]

Is there anyway to prevent certain sheets from be deleted?

i know there is protect workbook, but that prevents the user from deleting any sheet

[VBA Noob]

See if example helps

Code used

In sheet1 module (right click sheet1 rab > select view code)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Code in a normal module

Please Login or Register  to view this content.

VBA Noob

[jubi23]

awesome, thanks man!

[jubi23]

so uh how do i turn it off? lol
i ran it on a sheet i do want to be able to delete and can't figure it out ha

[VBA Noob]

Code is triggered when you select or unselected sheet1. Unselect sheet1 to turn off

VBA Noob

[jubi23]

so uh, i tried making a new workbook entirely, and the macro followed it there

for example, lets say i select sheet3, and then right click to try and delete, it pops open the excel with the preventdelete macro and doesn't allow me to do even if i disable macros

[VBA Noob]

Shouldn't work like that. Is it my example or your workbook

VBA Noob

[jubi23]

well i had my workbook open
and then i open your example, ran the macro
and now it seems to have applied itself to ever other workbook lol

[VBA Noob]

Yes. As Sheet1 was still active in my example when you switched to the other workbook it would still be on.

Try adding this code to thisworkbook module

Please Login or Register  to view this content.

VBA Noob

[jubi23]

OOOOOO okay
i get it now
in my other workbook i didn't have sheet 1 active but things still wouldn't delete and i was mad confused lol

so in your code, where is it decide that if its sheet1 is active you can't delete
because i want to try and edit your code so that it doesn't have to be sheet1, it could be for specific sheets that i don't one deleted
like sheets 2, 3 and 7
it'll be different for every user using my excel template

[VBA Noob]

The code goes in the sheet module and is activated when you select the sheet. So if you add the code to say Sheet 1 and sheet 2 it will call the delete macro message and stop the user deleting either of those sheets

VBA Noob

[VBA Noob]

jubi23,

Please read forum rules below and the message to cross posters then add the link to the cross post.

VBA Noob

[jubi23]

ah okay, sorry about cross posting!
never really thought about

thanks again for all your help