Thank you in advance for your help. I have a workbook that has a formula connected to Pi system.
Goal:
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.
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