Please help with VBA code

[Anupam9]

Hello all, i am trying to use a vba code to lock cells based on another cell value and facing difficulty. What i am trying to do is..
I have a dropdown in cell E4 with 4 selection e.g. A, B, C, D and i am trying to lock cell from E28 to E78 if i select A and G28 to G78 if i select B. when selecting C or D i need all cells to be unlocked.
I have unlocked E4 and protected entire sheet.
I would be very thankful for any assistance.

 

[mumps]

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. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in E4.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> "$E$4" Then Exit Sub
    ActiveSheet.Unprotect Password:="MyPassword"
    Select Case Target.Value
        Case "A"
            Range("E28:E78").Locked = True
        Case "B"
            Range("G28:G78").Locked = True
        Case "C", "D"
            Range("E28:E78,G28:G78").Locked = False
    End Select
    ActiveSheet.Protect Password:="MyPassword"
End Sub

 

[Anupam9]

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. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in E4.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> "$E$4" Then Exit Sub
    ActiveSheet.Unprotect Password:="MyPassword"
    Select Case Target.Value
        Case "A"
            Range("E28:E78").Locked = True
        Case "B"
            Range("G28:G78").Locked = True
        Case "C", "D"
            Range("E28:E78,G28:G78").Locked = False
    End Select
    ActiveSheet.Protect Password:="MyPassword"
End Sub

 

[Anupam9]

Thank you for replying. I am getting error that unable to set locked property of the range class. When i am selecting new site in E4 then i am not getting error but the cells are not getting locked. I have attached an image here

 

[mumps]

I tested the macro on a dummy file and it worked properly. Could upload a copy of your file to a free site such as www.box.com or www.dropbox.com? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Anupam9]

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. Change the password (in red) to suit your needs. Close the code window to return to your sheet. Make a selection in E4.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address <> "$E$4" Then Exit Sub
    ActiveSheet.Unprotect Password:="MyPassword"
    Select Case Target.Value
        Case "A"
            Range("E28:E78").Locked = True
        Case "B"
            Range("G28:G78").Locked = True
        Case "C", "D"
            Range("E28:E78,G28:G78").Locked = False
    End Select
    ActiveSheet.Protect Password:="MyPassword"
End Sub

I tested the macro on a dummy file and it worked properly. Could upload a copy of your file to a free site such as www.box.com or www.dropbox.com? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Box

app.box.com

This is box link. E4 is selection and would like to lock E28:E76 when selected new site and lock H28:H76 when selecting Activation

 

[mumps]

The problem is caused by the merged cells in your sheet. Re-design the sheet by eliminating all the merged cells and try the macro again. You should avoid using merged cells at all cost because they almost always cause problems for macros.

 

[Anupam9]

The problem is caused by the merged cells in your sheet. Re-design the sheet by eliminating all the merged cells and try the macro again. You should avoid using merged cells at all cost because they almost always cause problems for macros.

Thank you so much. I have corrected file and code is working now. Thank you again.

 

[mumps]

You are very welcome.