excelvbapasswordsconstantsglobal

Setting and Declaring Global Constant

Upon opening an Excel workbook, I declare and set a global constant.

Private Sub Workbook_Open()
    Application.Run "SetConstants"
End Sub

Sub SetConstants()
    Const Pass As String = "QEOps"
End Sub

Sub Protect()
    ThisWorkbook.Worksheets("Start").Protect Password:=Pass
    ThisWorkbook.Worksheets("Cognos Trans by Date w Lot").Protect Password:=Pass, AllowSorting:=True, 
End Sub

Sub Unprotect()
    ThisWorkbook.Worksheets("Start").Unprotect Password:=Pass
    ThisWorkbook.Worksheets("Cognos Trans by Date w Lot").Unprotect Password:=Pass
End Sub

When I put a MsgBox in workbook open, it shows the password.

When I manually try to unprotect the worksheets, they have no password.

Solution

  • You need to insert the new module into the project and declare this constant there:

    Public Const Pass As String = "QEOps"

    Use "Option Explicit" to avoid non-declared variables.

    In your code Pass is the local constant or variable in every subroutine.

    Consider other concerns to store the password by such way.