Preventing copy/move sheet INTO main workbook from outside workbook

[PaulZak]

I'm using this Workbook's SaveSheet event to prevent users from adding a new sheet from within my main workbook file. OK, that's half the goal.

But that event isn't triggered if user tries to copy/move a sheet from another WB into my main file.

I've started looking at a workbook lock/protect setting but we have numerous procedures that add temp sheets in workbook for data sheets, charts, etc. So, if I do something like lock or protect the workbook, we have to find each such instance when sheets are added, changed, deleted and possible add code to unlock/unprotect, add sheet, relock/protect each time. This isn't ideal.

Any suggestions for how to prevent users from copying/moving a sheet into the main workbook?

 

[Jaafar Tribak]

If you can't protect the workbook then you may want to try the following vba workaround which should prevent the user from copying/moving sheet(s) coming from other workbooks into the main workbook but still allow sheets to be copied/moved from within the main workbook.


Code goes in the ThisWorkbook Module of the main workbook:

Option Explicit

Private bSheetCopiedOrMovedFromOtherWorkbook As Boolean
Private oLastActiveSheet As Worksheet


Private Sub Workbook_Activate()
    bSheetCopiedOrMovedFromOtherWorkbook = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set oLastActiveSheet = Sh
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    On Error GoTo errHandler
    
    With Application
        If bSheetCopiedOrMovedFromOtherWorkbook Then
            bSheetCopiedOrMovedFromOtherWorkbook = False
            .EnableEvents = False
            .ScreenUpdating = False
            .DisplayAlerts = False
            Sh.Delete
            If Not oLastActiveSheet Is Nothing Then
                oLastActiveSheet.Activate
            End If
            MsgBox "Operation not allowed"  [COLOR=#008000][B]'<=== You Can Remove This Optional MsgBox.[/B][/COLOR]
        End If
        
errHandler:
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[PaulZak]

Thanks. Great idea. I'll give it a try.