Please help with VBA code

Anupam9

New Member
Joined
Jun 5, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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.
Rich (BB code):
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

New Member
Joined
Jun 5, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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.
Rich (BB code):
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

New Member
Joined
Jun 5, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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
 

Attachments

  • IMG_20210605_212944-800x600.jpg
    IMG_20210605_212944-800x600.jpg
    111.3 KB · Views: 7

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624

ADVERTISEMENT

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

New Member
Joined
Jun 5, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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.
Rich (BB code):
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.
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,624

ADVERTISEMENT

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

Anupam9

New Member
Joined
Jun 5, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,061
Members
421,325
Latest member
tapete86

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