Controlling the Workbook

Controlling The Workbook

Disable Uncontrolled Copy

Option Explicit
Private Sub Workbook_Open()
Dim filePathName As StringfilePathName = Application.ActiveWorkbook.FullName
Dim currentSheet As WorksheetIf filePathName = "Y:\ENG - Engineering Files\860\100 Repair Log Report\ENG.860-100.xlsm" Then ' Official file: unhide everything For Each currentSheet In Worksheets 'currentSheet.visible = xlSheetVisible currentSheet.visible = xlSheetVisible Next ' hide log 'Sheets("log").visible = xlVeryHidden ' For development: make log visible Sheets("log").visible = xlSheetVisible Sheets("File Disabled").visible = xlVeryHiddenElse ' Uncontrolled file: hide everything For Each currentSheet In Worksheets If currentSheet.name = "File Disabled" Then currentSheet.visible = xlSheetVisible Else currentSheet.visible = xlVeryHidden End If Next MsgBox ("This file has been disabled because it is an uncontrolled copy.")End If
End Sub

Check User

Option Explicit
Private Sub Workbook_Open()
Dim userName As String
userName = Environ$("username")userName = LCase(userName)
If userName = "myusername" ThenMsgBox ("Hello " & userName & "! Enjoy the document. :) ")End If
End Sub

Check User From List

Private Sub Workbook_Open()'06JUL2020
'Enforces document control.'Only runs when the document is first opened.
'Enable eventsApplication.EnableEvents = True
'Update loglog.logSheet ("open")
'SettingsDim MAX_NUMBER_OF_USERS As LongMAX_NUMBER_OF_USERS = 20
Dim MAX_DOCUMENT_CONTROL_FIELDS As LongMAX_DOCUMENT_CONTROL_FIELDS = 10
'VariablesDim x As IntegerDim userlistCol As LongDim enableDocument As BooleanDim ws As WorksheetDim userName As StringDim documentControlSheet As Worksheet
Set documentControlSheet = ActiveWorkbook.Sheets("DocumentControls")
'Get usernameuserName = Environ$("username")userName = LCase(userName)
'Loop through the columns looking for the userlistFor x = 1 To MAX_DOCUMENT_CONTROL_FIELDS If (documentControlSheet.Cells(1, x).Value = "userlist") Then userlistCol = x x = MAX_DOCUMENT_CONTROL_FIELDS End IfNext
'Loop through userlist to see if the document should be enabledenableDocument = FalseFor x = 1 To MAX_NUMBER_OF_USERS If (userName = documentControlSheet.Cells(x, userlistCol).Value) Then enableDocument = True End IfNext
'Document controlIf (enableDocument) Then 'Enable the document For Each ws In Worksheets If ( _ ws.name = "File Disabled" Or _ ws.name = "DocumentControls" Or _ ws.name = "CodeTable" Or _ ws.name = "ValidationLists" Or _ ws.name = "log" _ ) Then ws.Visible = xlSheetVeryHidden Else ws.Visible = xlSheetVisible End If Next MsgBox ("Hello " & userName & "! Enjoy the document. :)")Else 'Disable the document For Each ws In Worksheets If (ws.name = "File Disabled") Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next MsgBox ("Hello " & userName & ", this document has been disabled.")End If
'Fully enable document if user is myselfIf userName = "myusername" Then For Each ws In Worksheets ws.Visible = xlSheetVisible Next MsgBox ("Hello " & userName & "! Enjoy the document. :)")End If
End Sub

On Open - Setup Worksheets

Option Explicit
Private Sub Workbook_Open()
Dim ws As WorksheetDim userName As String
userName = Environ$("username")userName = LCase(userName)
If userName = "myusername" Then For Each ws In Worksheets ws.Visible = xlSheetVisible Next MsgBox ("Hello " & userName & "! Enjoy the document. :) ")Else For Each ws In Worksheets ws.Visible = xlSheetVisible If (ws.Name = "File Disabled") Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End If Next MsgBox ("This document is not ready for review.")End If
End Sub

On Close - Setup Worksheets

Private Sub Workbook_Close()
Dim ws As Worksheet
For Each ws In Worksheets ws.Visible = xlSheetVisible If (ws.Name = "File Disabled") Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End IfNext
End Sub

OnClose - Setup and Save

Private Sub Workbook_BeforeClose(Cancel As Boolean)'Nolan Manteufel'06JUL2020
'Enforces document control on close
'Log closelog.logSheet ("close")
'Limit sheet visibilitiesDim ws As WorksheetFor Each ws In Worksheets If (ws.name = "File Disabled") Then ws.Visible = xlSheetVisible Else ws.Visible = xlSheetVeryHidden End IfNext
'Save workbookActiveWorkbook.Save
End Sub

Page updated

Google Sites

Report abuse