ExcelBanter - VBA code for Locking Cell disabling Drop Down 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:

Code:

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

I am unable to upload the file to this website due to security restrictions but it can be found he
https://rcpt.yousendit.com/143737095...889d657f64b494

Many Thanks,
Leox5000