Forum Discussion
prevent copy sheet inside a workbook
I created a costing workbook consisting of several worksheets; there is one particular sheet that is the template, it is worksheet protected but user can input data to it, what I want is to prevent this template from being copied or duplicated within the workbook - this would mess up the program..pressing Ctrl key at the same time mouse dragging the sheet tab to the right will copy/duplicate the sheet. HOW CAN I PREVENT THIS COPY SHEET (also, preventing RIGHT CLICK?) just to this specific worksheet.
Thanks a lot
You could try this code in ThisWorkbook:
Option Explicit Dim msOldsheet As String Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name Like msOldsheet & " (#)" Or Sh.Name Like msOldsheet & " (##)" Then MsgBox "Copying this sheet is not allowed!" Application.DisplayAlerts = False Application.EnableEvents = False msOldsheet = "" Sh.Delete Application.DisplayAlerts = True Application.EnableEvents = True End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 'Add a test here to check if this is the sheet you didn't want copied If Sh.Name = "TheSheetNotToCopy" Then msOldsheet = Sh.Name End If End Sub
You could try this code in ThisWorkbook:
Option Explicit Dim msOldsheet As String Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name Like msOldsheet & " (#)" Or Sh.Name Like msOldsheet & " (##)" Then MsgBox "Copying this sheet is not allowed!" Application.DisplayAlerts = False Application.EnableEvents = False msOldsheet = "" Sh.Delete Application.DisplayAlerts = True Application.EnableEvents = True End If End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 'Add a test here to check if this is the sheet you didn't want copied If Sh.Name = "TheSheetNotToCopy" Then msOldsheet = Sh.Name End If End Subthank you for your reply
Jan Karel Pieterse has provided you an excellent piece of code that I have already embedded into your workbook among the other changes you have requested via other posts.
