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

intern1

New Member
Joined
Jun 23, 2018
Messages
9
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.
 
Here is a pseudo_event that intercepts the right click of the designated worksheet tabs, displays a MsgBox when choosing delete and prevents deletion ... The pseudo-event fires before the deletion of the worksheet(s) take place.

This worked for me in excel 2010.

Code goes in the ThisWorkbook Module:
Code:
Option Explicit

Private WithEvents cmbrs As CommandBars

Private Type POINTAPI
    x As Long
    Y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
        Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal Hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal Hwnd As LongPtr) As Long
    Private Hwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare Function IsWindowVisible Lib "user32" (ByVal Hwnd As Long) As Long
    Private Hwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const CHILDID_SELF = &H0&
Private Const S_OK As Long = &H0


[B][COLOR=#006400]'***************************************************************
'*                       Pseudo-Event                          *
'***************************************************************[/COLOR][/B]
Private Sub Pseudo_Worksheet_BeforeDelete_Event(ByVal Selected_Sheets As Sheets, ByRef Cancel As Boolean)
    Dim vArray As Variant
    Dim oSh As Object
    
    vArray = Array("Training Spreadsheet", "Training List", "Report Options", "Data-do not delete")
    For Each oSh In Selected_Sheets
        If Not IsError(Application.Match(oSh.Name, vArray, 0)) Then
            Cancel = True
            MsgBox "Can't delete the sheet :- '" & oSh.Name & "'", vbCritical
            Exit Sub
        End If
    Next
End Sub
[B][COLOR=#006400]'*****************************************************************[/COLOR][/B]


Private Sub Workbook_Open()
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set cmbrs = Nothing
End Sub


Private Sub cmbrs_OnUpdate()
    Dim sArray() As String
    Dim sBuf As String * 256
    Dim lRes As Long, i As Long
    Dim bCancelDelete As Boolean
    
    If Not ActiveWorkbook Is Me Then Exit Sub
    Application.CommandBars.FindControl(ID:=2020).Enabled = Not Application.CommandBars.FindControl(ID:=2020).Enabled
    lRes = GetClassName(GetActiveWindow, sBuf, 256)
   [COLOR=#006400] 'If Left(sBuf, lRes) = "wndclass_desked_gsk" Then Set cmbrs = Nothing:  Exit Sub '<== Optional line.[/COLOR]
    If Left(sBuf, lRes) = "Net UI Tool Window" Then
        For i = 0 To Me.Worksheets.Count - 1
            ReDim Preserve sArray(i)
            sArray(i) = Me.Worksheets(i + 1).Name
        Next
        If Not IsError(Application.Match(GetAccUnderMouse, sArray, 0)) Then
            Hwnd = GetActiveWindow
            Do
                If Replace(Application.CommandBars.FindControl(ID:=5858).Caption, "&", "") = GetAccUnderMouse Then
                    If GetAsyncKeyState(VBA.vbKeyLButton) < 0 Then
                        Call Pseudo_Worksheet_BeforeDelete_Event(ActiveWindow.SelectedSheets, bCancelDelete)
                        If Not bCancelDelete Then ActiveWindow.SelectedSheets.Delete
                    End If
                End If
                DoEvents
            Loop Until IsWindowVisible(Hwnd) = 0
        End If
    End If
End Sub

Private Function GetAccUnderMouse() As String
    Dim tPt As POINTAPI, oIA As IAccessible, lRes As Long
    
    GetCursorPos tPt
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Dim lngPtr As LongPtr
        CopyMemory lngPtr, tPt, LenB(tPt)
        lRes = AccessibleObjectFromPoint(lngPtr, oIA, 0)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        lRes = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, 0)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    If lRes = S_OK Then GetAccUnderMouse = oIA.accName(CHILDID_SELF)
End Function

 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi,

In which excel version was this event introduced ? Excel 2010 has no such event .
If it's not in 2010, it must have been introduced with 2013.
Although it would have helped if they had included a Cancel as Boolean argument with it.
 
Upvote 0
If it's not in 2010, it must have been introduced with 2013.
Although it would have helped if they had included a Cancel as Boolean argument with it.

It actually seems kind of pointless without a Cancel option.:)
 
Upvote 0
I don't know what version introduced it, i'm using 2016

I have found that every time I try to delete a worksheet, it is renamed with a number at the end. TrainingList1, TrainingList2, etc. I can delete that number and then all of my macros run correctly. Is there a way to build that correction into the sheet code? I tried recording a macro while I renamed it, but I think it's too specific.
ActiveSheet.ListObjects("TrainingList2").Name = "TrainingList"

It is not always changed to "TrainingList2" and I don't know how to get around that.
 
Upvote 0
Have you tried Jaafar's code. As that would eliminate your problem
 
Upvote 0
How about
Code:
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

Hello Fluff, sorry for re-asking on an old thread.

I am having issues trying to use this code and ending not having #REF! values resulting on other sheets not deleted depending on values of the deleted sheet. I have tried to add .Calculation = XlCalculation.xlCalculationManual so it can stop the formulas to update while replacing a copy of the sheet with your code but luck. Any idea to prevent the #REF! results on dependencies?

Any help will be greatly appreciated.

Regards.
 
Upvote 0
Try Jaafar's code as that will eliminate the problem.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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
Back
Top