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

  • 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.


  • 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.

    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

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!