Reference Cells For Passwords in Code

Try:

Dim formprotect As String: formprotect = Sheets("Search").Range("D14").Value
Dim Clerkpw As String:  Clerkpw = Sheets("Search").Range("D15").Value

I see this, and many of your other threads are marked solved, although I'm not sure we reached a conclusion for this one.

For closure, if only mine, here is the way I think it could/should be done.

Declare the Global Variables at the start of ModPWSecurity standard module

' Public/Global Variables
Dim formprotect As String
Dim Clerkpw As String
Dim NewAFRpw As String

' Original Constants; no longer required
'Const formprotect = "pass"
'Const Clerkpw = "clerk"
'Const NewAFRpw = formprotect

Then, in the Workbook module, change the Workbook_Open event handler:

Private Sub Workbook_Open()

formprotect = Sheets("Search").Range("D14").Value
Clerkpw = Sheets("Search").Range("D15").Value
NewAFRpw = formprotect

'Stop    ' for testing purposes to check password values are loaded as expected.

Call FormLock

End Sub