• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to Protect and Unprotect columns depending on dropdown value

GauravS313530

New Member
Hi All, I don't get any solution in google for below scenario.

In attached excel, there are 2 dropdown list in sheet "Home" and I want to Protect/Unprotect "Data Sheet" columns basis on these dropdown values.

when I select "Budget" in dropdown then it will Unprotect columns(from F to Q) in Data sheet means all columns are editable. and when I select "Forecast" In dropdown then It will start Protecting Columns basis on Second dropdown List Value. for ex.- If I Select "Forecast" and "May" in Dropdown then It will Protect columns (F to I) so that No one can change the data in these columns (F to I). If I select "Forecast" and "Nov" In dropdown , it will Protect columns (F to O) so that these are not editable.

Protection can with/without password protected.

Request all to Please help with the solution.
 

Attachments

rollis13

Member
If I understood your request correctly, this macro, to be pasted into the Home module, does what you asked. Before you begin, you need to make sure that the cells in the entire sheet are formatted Unlocked except for other cells, outside the F:Q, range that you need locked for other reasons.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Row = 2 Or Target.Row = 5 Then
        With Sheets("Data")
            .Unprotect 'Password:="test123"
            .Columns("F:Q").Locked = False
            If Cells(2, 3) = "Forecast" Then
                Select Case Cells(5, 3)
                    Case Is = "Feb'22"
                        .Columns("F:F").Locked = True
                    Case Is = "Mar'22"
                        .Columns("F:G").Locked = True
                    Case Is = "Apr'22"
                        .Columns("F:H").Locked = True
                    Case Is = "May'22"
                        .Columns("F:I").Locked = True
                    Case Is = "Jun'22"
                        .Columns("F:J").Locked = True
                    Case Is = "Jul'22"
                        .Columns("F:K").Locked = True
                    Case Is = "Aug'22"
                        .Columns("F:L").Locked = True
                    Case Is = "Sep'22"
                        .Columns("F:M").Locked = True
                    Case Is = "Oct'22"
                        .Columns("F:N").Locked = True
                    Case Is = "Nov'22"
                        .Columns("F:O").Locked = True
                    Case Is = "Dec'22"
                        .Columns("F:P").Locked = True
                End Select
            End If
            .Protect 'Password:="test123"
            .EnableSelection = xlUnlockedCells
        End With
    End If
End Sub
This could be a variant wanting to make the Select transparent per year (ready for next year):
Code:
'...
If Cells(2, 3) = "Forecast" Then
    Select Case Left(Cells(5, 3), 3)
        Case Is = "Feb"
            .Columns("F:F").Locked = True
        Case Is = "Mar"
            .Columns("F:G").Locked = True
        Case Is = "Apr"
            .Columns("F:H").Locked = True
'etc.
'...
 
Last edited:
Top