Results 1 to 3 of 3

Thread: Preventing copy/move sheet INTO main workbook from outside workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2006
    Location
    Arlington, VA
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Preventing copy/move sheet INTO main workbook from outside workbook

    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?

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,398
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Preventing copy/move sheet INTO main workbook from outside workbook

    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"  '<=== You Can Remove This Optional MsgBox.
            End If
            
    errHandler:
            .EnableEvents = True
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
        
    End Sub
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    Board Regular
    Join Date
    Aug 2006
    Location
    Arlington, VA
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing copy/move sheet INTO main workbook from outside workbook

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •