VBA to unlock worksheet

[hayleyzim]

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.

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:

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."?

 

[JoeMo]

Try replacing your code with this:

Sub Unlock_CourseList()
Dim response As String
Dim current_user As String
Dim match_search As Variant
current_user = Environ("Username")
On Error Resume Next
match_search = Application.Match(current_user, Range("CourseListAuthorities"), 0)
    If IsError(match_search) Then
        On Error GoTo 0
        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

 

[hayleyzim]

Try replacing your code with this:

Sub Unlock_CourseList()
Dim response As String
Dim current_user As String
Dim match_search As Variant
current_user = Environ("Username")
On Error Resume Next
match_search = Application.Match(current_user, Range("CourseListAuthorities"), 0)
    If IsError(match_search) Then
        On Error GoTo 0
        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

That fixed it, thank you so much for your help!

 

[JoeMo]

That fixed it, thank you so much for your help!

You are welcome - thanks for the reply.