CODEWITHSUNDEEP
excelvbapasswordsconstantsglobal
SamB
SamB

Reputation: 5

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.

Upvotes: 0

Views: 88

Answers (1)

rotabor
rotabor

Reputation: 4653

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.

Upvotes: 0

Related Questions