VBA code to conditionally lock specific cells

beth6441

New Member
Joined
Feb 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to do VBA code to conditionally lock specific cells based on the content of another cell.

Not having much luck...any help appreciated!

This is what I tried - but I can't get it to work:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim cel As Range
    Dim r As Variant
    If Not Intersect(Range("D9:M9"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Me.Unprotect Password:="mypassword"
        For Each cel In Intersect(Range("D9:M9"), Target)
            ' Unlock all cells
            For Each r In Array(16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 45, 47, 49, 51, 53, 55, 57, 59, 61, 63, 65, 67, 70, 72, 74, 76)
                With Cells(r, cel.Column)
                    .Locked = False
                    .ClearContents
                End With
            Next r
            Select Case cel.Value
                Case "YES"
                    For Each r In Array(18, 26, 30, 34, 38, 47, 49, 53, 74, 76, 78)
                        Cells(r, cel.Column).Locked = True
                    Next r
                Case "NO"
                    For Each r In Array(61)
                        Cells(r, cel.Column).Locked = True
                    Next r
               
            End Select
        Next cel
        Me.Protect Password:="mypassword"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
The last bit of your code seems to lock the cells for both Case "Yes" and Case "No". Shouldn't one of them be

VBA Code:
Cells(r, cel.Column).Locked = False
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

beth6441

New Member
Joined
Feb 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you for the suggestion. I figured out an alternative method by creating a formula, creating a unique name for the formula in Name Manager and then using Data Validation. The cell containing data validation will then display a list if D9=YES, otherwise it displays "".
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,673
Messages
5,637,717
Members
416,981
Latest member
PLonchar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top