• 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

G

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

  • Book1.xlsm
    18 KB · Views: 4
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:
The macro is an event macro and works only with the sheet in which it has been pasted. To do the same in another sheet all you have to do is duplicate it (Copy/Paste) in the other sheet's module. If necessary adjust the cell/column ranges and password if they differ.
 
Hi
rollis13
in another module/Macro I need to create a button when I click on the button it will unprotect the Data sheet and copy the whole data with Heading and Paste it in another sheet(Previous data) and the again protect the Data sheet as it was earlier protected.
 
This is a completely different case and the two macros will coexist. The first macro, topic of this thread, will trigger automatically whenever you use the dropdowns in the target cells, while this other macro will be launched only when you click the button you created.
If you need help with this other new macro I suggest opening a new thread with a suitable title.
 
rollis13 said:
This is a completely different case and the two macros will coexist. The first macro, topic of this thread, will trigger automatically whenever you use the dropdowns in the target cells, while this other macro will be launched only when you click the button you created.
If you need help with this other new macro I suggest opening a new thread with a suitable title.
Click to expand...


thanks for your prompt replies and suggestions.
I have added a line in my new macro and it is working ok as of now.
>>> use code - tags <<<
Code: 
Application.Run "Sheet1.Worksheet_Change", Range("A1")
 
Last edited by a moderator:
rollis13 said:
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.
'...
Click to expand...

Can we apply the same code in another sheet named as (ARVE) in same workbook basis on the same dropdown list in home tab.
 
To use this macro in two different sheets but dependent on the same drop-down list in the "Home" sheet you could duplicate the necessary rows, it's not elegant but it will work:
Code: 
'...
With Sheets("Data")
    .Unprotect                            'Password:="test123"
    Sheets("ARVE").Unprotect              'Password:="test456"
    .Columns("F:Q").Locked = False
    Sheets("ARVE").Columns("F:Q").Locked = False
    If Cells(2, 3) = "Forecast" Then
        Select Case Left(Cells(5, 3), 3)
            Case Is = "Feb"
                .Columns("F:F").Locked = True
                Sheets("ARVE").Columns("F:F").Locked = True
            Case Is = "Mar"
                .Columns("F:G").Locked = True
                Sheets("ARVE").Columns("F:G").Locked = True
            Case Is = "Apr"
'... etc.
 
You must log in or register to reply here.
Back
Top