Preventing copy/move sheet INTO main workbook from outside workbook

PaulZak

Board Regular
Joined
Aug 22, 2006
Messages
104
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

Well-known Member
Joined
Dec 5, 2002
Messages
7,561
Office Version
2016
Platform
Windows
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:
Code:
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
 

Forum statistics

Threads
1,085,931
Messages
5,386,824
Members
402,020
Latest member
tsfan74

Some videos you may like

This Week's Hot Topics

Top