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.
'---------------------------
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