Drop Menu with VLookup on Locked Cells solution!?

[Margate]

Hello Everyone,

I have a sheet that has a drop down menu. The list (drop down menu) contains names of staff that is updated all the time. Once the name of the member of staff is selected from the drop down menu the next cell is auto populated with the I.D. number by using VLookup. This works with no problem until I lock the cells and protect the sheet.

Now when I select a name from the drop down menu I am met with the following error message: The cell or chart you are trying to change is protected and therefore read only.....

I had a similar problem before that was solved (via a post on this forum) but will not work in this case because the drop menu described above contains names that change all of the time. In the code below the drop menu just contained numbers (1, 2,) that would trigger a macro (DayOne, Day Two).

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C6" Then

Select Case Target.Value

Case 1
Call DayOne

Case 2
Call DayTwo

End Select
End If
End Sub


I was thinking that there may be a way to modify the above code so that it would be able to unlock the sheet populate the cells and re-lock the sheet again?

Thank you in advance for any help,

Regards,

Margate

[bheanloh]

To protect and unprotect sheet

Please Login or Register  to view this content.

[Margate]

Hello Bheanloh,

Thank you for the reply. I need however to activate this macro whenever a choice is made from the drop menu?