CODEWITHSUNDEEP
excelvbaworksheet
KingKong
KingKong

Reputation: 421

Detect the renaming or deletion of worksheets

Is there a way to detect when a user

I want to run some code if one of these events happens.

what I have tried

My tool uses a lot of event handlers so one thing I thought of was looping through all the sheetnames during each Worksheet_Change, but I don't think that is the best approach.

Upvotes: 2

Views: 70

Answers (1)

brettdj
brettdj

Reputation: 55692

This approach goes under the ThisWorkbook module.

Public shArray1 As Variant
Public shArray2 As Variant

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim lngCnt As Long
Dim strMsg As String
Dim strSht
Dim vErr
Dim strOut As String

'get all sheet names efficiently in a 1D array
ActiveWorkbook.Names.Add "shtNames", "=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(""]"",GET.WORKBOOK(1)))"
shArray2 = Application.Transpose([INDEX(shtNames,)])
strSht = Application.Transpose(Application.Index(shArray2, , 1))

'exit here if first time code is run
If IsEmpty(shArray1) Then
    shArray1 = shArray2
    Exit Sub
End If

`check each sheet name still exists as is  
For lngCnt = 1 To UBound(shArray1)
   vErr = Application.Match(shArray1(lngCnt, 1), strSht, 0)
   If IsError(vErr) Then
        strOut = strOut & shArray1(lngCnt, 1) & vbNewLine
        vErr = Empty
  End If
Next

shArray1 = Application.Transpose([INDEX(shtNames,)])

If Len(strOut) > 0 Then MsgBox strOut, vbCritical, "These sheets are gone or renamed"

End Sub

Upvotes: 1

Related Questions