Hello! I am relatively new to VBA coding, and all that I know I learned from the internet - copying codes and manipulating them to do what I want....
I have a code I need help on. Here is the situation: I have a Purchase Order workbook that each of our department has. I am currently using one as a test mode. On this one, we are trying to create a VBA to alert when the user enters data on the purchase order worksheet (sheet 1) and when that amount increases the monthly budget for a given expense, an alert is shown. Right now, my code is looking at the total spending for a month from sheet 12. A particular cell for one expense (eventually I would like it to evaluate all expenses per month). My test data I am utilizing is 41.67 is the max amount, so I am entering 50 in cell N4 of sheet 1 (the purchase order detail screen). For the most part - it does what I want. After entering 50, which is obviously above the 41.67 threshold, a msgbox appears alerting the user it has gone over budget for the expense. There are two options, click Yes if you have supervisor approval and needed password, and no cancels, then removes the total from cell N4 (I would like to also read this row by row so that if I happen to be entering in N45, it will erase the contents from that cell. Anyway. I click No, then it messages that the amount will be cleared, and the amount is cleared. If I choose Yes, another prompt advising a password is requested, with Ok. Ok button proceeds to an input box for a password (currently "password"). If I enter it incorrectly, another message advises password is not correct, and total will be removed from cell N4. If I enter correctly, msg advising password is correct, click ok and then I'm done. However, because the total is STILL over that threshold amount, everytime I click or tab in the sheet, it starts the vba code all over again. I want the code to ignore the overbudget amount once the password is entered. Is there anyway to do this? I believe there is......but I'm missing it somewhere.....
Any other questions you all may have for me in order to help me figure this out would be appreciated!
Hi Lori,
You can store the password verification result in a public or global parameter, but the problem is that these parameters are lost when an error is thrown.
You can try to store in registry. The following code will replace our password verification (the changes are in red):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pwd As String
Dim Approved As Boolean, Check As String
Check = GetSetting("Gage Budget", "Expense", "Approval", "")
If Check = "Approved" Then Approved = True
Application.EnableEvents = False
If Approved = False Then
If Sheet12.Range("C5").Value > 41.66 Then
Response = MsgBox("You have exceeded the monthly budget for Gage-Calibration expense." & vbNewLine & "You must have your Department Head to approve in order to continue." & vbNewLine & "Do you have a password to Continue?", vbYesNo + vbCritical, "STOP! Budget Exceeded!")
If Response = vbYes Then
Answer = MsgBox("Password Needed", vbOKCancel)
If Answer = vbOK Then
pwd = Application.InputBox("Enter Password")
If pwd <> "password" Then
MsgBox ("Incorrect Password." & vbNewLine & "Amount will be cleared.")
Range("N4").ClearContents
Else
MsgBox "Password Success"
SaveSetting "Gage Budget", "Expense", "Approval", "Approved"
End If
End If
ElseIf Response = vbNo Then
MsgBox "Amount to be cleared."
Range("N4").ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub
This setting must be reset when the book is closed, put this in ThisWorbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
SaveSetting "Gage Budget", "Expense", "Approval", ""
End Sub
