Locking out Cells once an option is selected in a drop down.

excelrich · Feb 6th 2020

I have a spreadsheet consisting of 6 'slots' and 7 fields within them. I have a field with a drop down menu in it. When the user selects 'Used Valet & Buff' in cell C5 I need from C9 to C22 to be locked out to allocate time for that specific job and not allow anyone else using this shared spreadsheet to allocate a job within those cells.

How would I go about doing this as I am new to excel macros and VBA?

Thanks in advance.

Richard

Mumps · Feb 6th 2020

Start by unlocking all the cells in your sheet. Then copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. In order for the cells to be locked, the sheet has to be protected. Change the password in the macro (in red) to a password of your choosing. Close the code window to return to your sheet. Make a selection in C5.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub
    If Target = "Used Valet & Buff" Then
        ActiveSheet.Unprotect Password:="MyPassword"
        Range("C9:C22").Locked = True
        ActiveSheet.Protect Password:="MyPassword"
    End If
End Sub

Locking out Cells once an option is selected in a drop down.

Participate now!

Share