Macro to show input box when a user tries to delete a worksheet

[intern1]

Hi there,
I have a shared workbook with 4 sheets that must not be deleted. I want users to be able to view, add and make changes, but people have already deleted these sheets in error and we've had to restore the workbook from an older version.

Other worksheets will be added and those can be deleted with no issues.

What I would like to do is create a msgbox or input box if they right click these worksheet tabs and choose delete. I figured out how to do both the msg and input, but I don't know how to start the macro. How can i get a macro that recognizes when they right clicked and chose delete?

These are my sheets that I need to prevent from being deleted:
Training Spreadsheet
Training List
Report Options
Data-do not delete

Any suggestions would be appreciated. I'm pretty new at this.

 

[Fluff]

How about

Private Sub Worksheet_BeforeDelete()
   Dim Nme As String
   Me.Copy , Sheets(Me.Index)
   Nme = Me.name
   Me.name = "XXXXXXXXXXXXXX"
   ActiveSheet.name = Nme
End Sub

This needs to go in the sheet module, for each sheet you want to keep.
It doesn't stop the deletion, but it's the next best thing

 

[intern1]

Thanks so much. I added a specific reminder in a msgbox, that will hopefully deter the next user from deleting the wrong sheet.

 

[Fluff]

Glad to help & thanks for the feedback

 

[intern1]

I do have another question about this. I was testing it out and the page does not delete, it renames to xxxxxxxx, shows the message box i created and then gives this:

"The name 'rng_Expenses20' already exists. Click Yes to use that version of the name, or click No to rename the version of 'rng_Expenses20' you're moving or copying."

I'm not sure where this is coming from; i don't have any page by that name. It still seems to work, although one page is a table and I have macros that will create pivot tables from that. My macros no longer work since this started. I have no idea if the message is related to the code above that I added. Thoughts?

 

[Norie]

Do you have any named ranges on the worksheets you want to prevent users deleting?

 

[intern1]

this is all I have in there:

Private Sub Worksheet_BeforeDelete()
Dim Nme As String
Me.Copy , Sheets(Me.Index)
Nme = Me.Name
Me.Name = "XXXXXXXXXXXXXX"
ActiveSheet.Name = Nme

MsgBox "Should You Be Deleting This Page?", vbInformation, "Caution"
End Sub

 

[Norie]

Is there anything listed when you open the Name Manager (CTRL+F3) on the fomulas tab?

 

[intern1]

Why yes there is--rng_Expenses20 is listed twice and one of my other sheets is listed as well.

Name: rng_Expenses20
Value: #Ref !
Refers to: =#Ref !#Ref !
Scope: Training spreadsheet.

Training spreadsheet is one of the sheets I'm trying to prevent them from deleting


originally I also tried doing this for a sheet labeled TrainingList, that has the table that all of my macros run off of. That is the third item listed.

Is there a way to make the code work? It almost seems like it's duplicating my sheet and then deleting a duplicate.
Sorry if these seem like dumb questions, I just started working with VBA about a month ago and I've still got a lot to learn.

 

[Jaafar Tribak]

How about

Private Sub Worksheet_BeforeDelete()
   Dim Nme As String
   Me.Copy , Sheets(Me.Index)
   Nme = Me.name
   Me.name = "XXXXXXXXXXXXXX"
   ActiveSheet.name = Nme
End Sub

This needs to go in the sheet module, for each sheet you want to keep.
It doesn't stop the deletion, but it's the next best thing

Hi,

In which excel version was this event introduced ? Excel 2010 has no such event .