VBA code for Locking Cell disabling Drop Downs Validation Lists

Hi,

I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:


Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock
StarLock = 19
EnLock = 46

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.CutCopyMode = False


If Target.Column = 1 Then
myrow = Target.Row

ActiveSheet.Unprotect
If Cells(myrow, 1) = "Vacancy" Then

Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select
'With Selection.Interior
'.ColorIndex = 16
'.Pattern = xlSolid
'End With
Selection.Locked = True
Cells(myrow, 1).Select
ActiveSheet.Protect
End If
' Else
If Cells(myrow, 1) = "Applicant" Then

Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select
'With Selection.Interior
'.ColorIndex = 34
'.Pattern = xlSolid
' End With
Selection.Locked = False
Cells(myrow, 1).Select
ActiveSheet.Protect
Else

' Range(Cells(myrow, Starlock), Cells(myrow, Enlock)).Select
'With Selection.Interior
'.ColorIndex = 2
'.Pattern = xlSolid
'End With
' Selection.Locked = False
' Cells(myrow, 1).Select
' ActiveSheet.Protect
End If
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.CutCopyMode = True

ActiveSheet.Protect
End Sub


Any help would be much appreciated.
I will attach a copy of the file when I get home as the works IT security is crashing the uploader

Many Thanks,
Leox5000

Still having issues uploading the spreadsheet direct so it can be found here:
https://rcpt.yousendit.com/143737095...889d657f64b494