Disable coping and saving a sheet to a new file

J_Smith · Jul 5th 2015

I have a relatively secure Excel document. I have disable printing the contents; however, I do not know how to disable copying the sheet to another workbook to stop printing the sheet.
The question: how to disable copying a sheet by clicking on the tab / selecting move or copy / to a new sheet (or other workbook)

Thanks in advance

j smith

yegarboy · Jul 6th 2015

Re: Disable coping and saving a sheet to a new file

Protect the Worksheet.

Click the "Review" tab. The click "Protect Sheet"

Uncheck the box that say "Select Unlocked Cells"

Enter a Password and your all set.

-Cheers

**Batman** · Jul 6th 2015

Re: Disable coping and saving a sheet to a new file

Hi J_Smith,

Welcome to the Ozgrid forum.

Yegarboy's solution will stop the users form selecting individual cells, and copying them to a separate worksheet/workbook. To prevent right-clicking the sheet tab to copy, go to the Review tab and click Protect Workbook. Make sure that the Structure box is ticked, and set a password.

J_Smith · Jul 6th 2015

Re: Disable coping and saving a sheet to a new file

Can't use that solution since the workbook is designed to receive data but not to distribute that data via a printed workbook.
I was able to put together a series of macros that, with 95% assurance, would maintain the security of the data.

Thaks for your input

js

**Batman** · Jul 6th 2015

Re: Disable coping and saving a sheet to a new file

Just for clarification, which solution can you not use? Protecting the workbook structure will only prevent you from adding, deleting, re-naming, re-ordering or copying worksheets. You can still work on the data within the sheet and save the workbook.

Smallman · Jul 7th 2015

Re: Disable coping and saving a sheet to a new file

J_Smith

It might be an idea to give back to the community. You came here asking for assistance and found your solution leaving others who may follow it no wiser. It would be a nice gesture to put your macro on this board.

Take care

Smallman

J_Smith · Jul 7th 2015

Re: Disable coping and saving a sheet to a new file

'The solution to the problem involved several macros which control various access elements.
'To provide security, it's necessary to
'-- Insure that all program macros are enabled
' When an Excel program is opened, a flag generally appears requesting whether to enable macros.
' To make sure that the macros are enabled, only one tab can be visible. A button/picture icon or
' similar device must be present which when clicked will open all of the other tabs. Obviously,
' if the user did not enable macros, the program is rendered useless.
' The icon should have the following properties: Locked: check Print: unchecked Name: Warning
'-- Disable printing.
'-- Disable Copy & Move tabs to another sheet.
'-- On closing or saving the program, all but primary tab must be hidden using the 'veryhidden' option.
'-- The code is placed in the Thisworkbook module.

Code

'---------------------------
Private Sub Workbook_Open()
'---------------------------
'-- Hide all but the primary tab: Cover Sheet
'-- Protect the primary tab (allow access onto to unprotected cells)
'-- Make visible a picture icon titled WARNING.
'-- Link the picture icon to a maco (hide Warning and make tabs visible)


    Dim ws
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Sheets(ws.Name).Activate
        If ws.Name <> "Cover Sheet" Then
            ActiveWorkbook.Sheets(ws.Name).Visible = xlSheetVeryHidden
        Else
            ActiveSheet.Shapes("Warning").Visible = True
            ActiveSheet.Shapes.Range(Array("Warning")).Select
            Selection.Locked = False
            Selection.OnAction = "Thisworkbook.hide_Warning"
            Selection.Locked = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
            True
        End If
    Next ws
    Application.ScreenUpdating = True
    Application.CommandBars("Ply").Enabled = False  '== Disable tab right click


End Sub


'--------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'--------------------------------------------------
    '== Very Hide worksheets except Cover Sheet
    '   Make WARNING advisory visible
    
    Dim ws
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Sheets(ws.Name).Activate
        On Error Resume Next
        ActiveSheet.Shapes("Warning").Visible = True
        '== "very" hide data sheets
        If ws.Name <> "Cover Sheet" Then
            ActiveWorkbook.Sheets(ws.Name).Visible = xlSheetVeryHidden
        End If
    Next ws
    On Error GoTo 0
    Application.ScreenUpdating = True


End Sub


'---------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'---------------------------------------------------------------------------------
    
    Call hide_all_data_sheets
    
End Sub


'---------------------------------------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'---------------------------------------------------
    
    Cancel = True
    MsgBox "You cannot print this workbook", vbOKOnly, "error"
    
End Sub


'========================
'-----------------
Sub hide_Warning()
'-----------------
    '== Hides WARNING; unhide all other tabs
        
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.Shapes("Warning").Visible = False
    Call unhide_all_data_sheets
    On Error GoTo 0
    Application.ScreenUpdating = True
    
End Sub


'------------
Sub unhide_all_data_sheets()
'------------
    Dim ws
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Sheets(ws.Name).Activate
        ActiveWorkbook.Sheets(ws.Name).Visible = True
    Next ws
    Sheets("Cover Sheet").Activate
    Application.ScreenUpdating = True


End Sub


'------------
Sub hide_all_data_sheets()
'------------
'== Run this program to "very" hide all worksheets except Cover Sheet
'== Can only be unhidden if macros are enabled


    Dim ws
    
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        Sheets(ws.Name).Activate
        If ws.Name <> "Cover Sheet" Then
            ActiveWorkbook.Sheets(ws.Name).Visible = xlSheetVeryHidden
        Else
            ActiveSheet.Shapes("Warning").Visible = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
            True
        End If
    Next ws
    Application.ScreenUpdating = True


End Sub

Display More

Smallman · Jul 7th 2015

Re: Disable coping and saving a sheet to a new file

Thanks for sharing with everyone. It was a very nice gesture.

Take care

Smallman

Disable coping and saving a sheet to a new file

Participate now!

Share

Users Viewing This Thread