[SOLVED] Workbook "Calculation Options" stays in Manual no matter the operation (open, save, close) being performed unless approved user hits Calculate

Workbook "Calculation Options" stays in Manual no matter the operation (open, save, close) being performed unless approved user hits Calculate

Thank you in advance for your help. I have a workbook that has a formula connected to Pi system.

Goal:

User is authenticated with a password
if password is wrong, workbook is closed
If correct workbook should open and stay in Manual mode until Calculate is activated.

I always want the "Calculation Options" of this workbook to stay in Manual no matter the operation (opening, saving, closing).

Below is a VBA code I adopted from reading in here but it is not working the way I want it. Workbook still opens when password is wrong. When I hit save in the workbook it starts to calculate.

I need help to modify the VBA code below to do what I want it to do above.


Private Sub Workbook_Open()
     uName = InputBox("Please type your username.", "Authentication Required", Environ("USERNAME"))
    uPwd = InputBox("Please type your password.", "Authentication Required")
On Error GoTo ErrorRoutine
If uPwd = Application.WorksheetFunction.VLookup(uName, Sheets("Password").Range("A:B"), 2, False) Then
'Password correct
Sheets("BE").EnableCalculation = False
Sheets("BE").Visible = True
Sheets("XGI Tags").Visible = True
Sheets("Password").Visible = xlVeryHidden
Sheets("KQI Tags").Visible = True

Else

'Password incorrect
ErrorRoutine:
msgReply = MsgBox("Password is incorrect.  Spreadsheet will be closed.", "Invalid Password", vbOKOnly)
ActiveWorkbook.Close (False)
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("BE").EnableCalculation = False
Sheets("BE").EnableCalculation = False
Sheets("BE").Visible = True
Sheets("XGI Tags").Visible = True
Sheets("Password").Visible = xlVeryHidden
Sheets("KQI Tags").Visible = True

End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Sheets("BE").Visible = True
End Sub

I have tried the code above and it is not working well.

Solution

You're using error handling for flow control, which isn't always a great idea: you could instead do something like this -

Private Sub Workbook_Open()
    Dim uName As String, uPwd As String, res, ok As Boolean, wb As Workbook

    uName = InputBox("Please type your username.", "Authentication Required", Environ("USERNAME"))
    uPwd = InputBox("Please type your password.", "Authentication Required")
    
    res = Application.VLookup(uName, ThisWorkbook.Worksheets("Password").Range("A:B"), 2, False)
    If Not IsError(res) Then ok = (uPwd = res) 'check password match
    
    Set wb = ThisWorkbook
    If ok Then
        'Password correct
        wb.Worksheets("BE").EnableCalculation = False
        wb.Worksheets("BE").Visible = True
        wb.Worksheets("XGI Tags").Visible = True
        wb.Worksheets("Password").Visible = xlVeryHidden
        wb.Worksheets("KQI Tags").Visible = True
    Else
        'Password incorrect
        MsgBox "Username and/or Password is incorrect.  Spreadsheet will be closed."
        wb.Close False
    End If
End Sub