Hello all! I'm trying to finish a spreadsheet setup that someone else had started, and I'm not sure how to fix this error occurring in their VBA.
The function is used to unlock a specific worksheet for editing, but only certain users are allowed access to this. We have an authority tab where we have listed which users are allowed to edit the worksheet, which the VBA has been setup to search. If the user is on the authority list, a message box pops up saying "Successfully unlocked Course List.". If they're not on the list, it should pop up a message box saying "You are not authorised to edit the Course List tab.".
This function is working perfectly if the person it authorised to edit, however when someone without authority tries to edit it comes up with an error.
It is highlighting this particular part as the error:
Can anyone please explain how I can fix this, so that if a person is not on the authority list then the message box will say "You are not authorised to edit the Course List tab."?
The function is used to unlock a specific worksheet for editing, but only certain users are allowed access to this. We have an authority tab where we have listed which users are allowed to edit the worksheet, which the VBA has been setup to search. If the user is on the authority list, a message box pops up saying "Successfully unlocked Course List.". If they're not on the list, it should pop up a message box saying "You are not authorised to edit the Course List tab.".
This function is working perfectly if the person it authorised to edit, however when someone without authority tries to edit it comes up with an error.
VBA Code:
Sub Unlock_CourseList()
Dim response As String
Dim current_user As String
Dim match_search As Variant
current_user = Environ("Username")
match_search = Application.Match(current_user, Range("CourseListAuthorities"), 0)
If match_search = "" Then
MsgBox ("You are not authorised to edit the Course List tab.")
Else
Worksheets("Course List").Unprotect Password:=PW_CourseList
MsgBox ("Successfully unlocked Course List.")
End If
End Sub
It is highlighting this particular part as the error:
VBA Code:
If match_search = "" Then
Can anyone please explain how I can fix this, so that if a person is not on the authority list then the message box will say "You are not authorised to edit the Course List tab."?