excelvbaformulaspreadsheet

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:

  1. User is authenticated with a password
  2. if password is wrong, workbook is closed
  3. 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