0

I am trying to implement a functionality in excel where I have a column 'I' with a dropdown of some values. I have an adjacent column 'J' which needs to be enabled only on other selection of 'I' column else disabled.

I am able to run the code for a single cell: On I2 selection of 'other' as the data then J2 gets disabled. That part works fine. I am trying to implement the similar functionality through the entire I and J column instead of just I2 and J2. Can someone please help me with the logic here.

New to VBA coding in excel, so please explain me with the code. Really appreciate the help.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)


 ActiveSheet.Unprotect

 If Range("I2").Value = "Urban Principal Arterial - Other" Or Range("I2").Value = "Rural Principal Arterial - Other" Or Range("I2").Value = "Urban Principal Arterial - Other F and E" Then
     Range("J$2").Locked = True
     Else: Range("$J$2").Locked = False
 End If


 ActiveSheet.Protect

 End Sub

Thanks, Dimpy

Community
  • 1
  • 1
Hari
  • 53
  • 2
  • 10
  • I recommend looking at how to format code in a stackoverflow question. This will make it nicer for others to read :) – Michael S Priz Jul 27 '15 at 16:06
  • 1
    I can give you the code but I want you to try it first using `Intersect`. You may want to see [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) – Siddharth Rout Jul 27 '15 at 21:03
  • Hi Siddharth, can you please share your code. Just want to see if your logic works for my scenario. I thought mine worked, but the sheet gets protected and becomes read-only after activesheet.protect. I don't have my code right now. Can post it in a while. Meanwhile can you post your code so I could test it and see. Please I need to complete this task ASAP. – Hari Jul 30 '15 at 11:02

1 Answers1

0

Thank you all for your responses, but I fixed it without intersect and my code works fine .

user3147594
  • 61
  • 1
  • 2
  • 14
  • Hi All: Please disregard the previous post. after testing my code extensively, I realized that my sheet would be just read-only due to activesheet.protect. I can post my code: – Hari Jul 30 '15 at 10:57
  • Have you unlocked the cells that will always need to remain editable? You should still be able to edit the unlocked cells even on a protected sheet, but I believe Excel defaults all cells to locked upon worksheet protection unless you specifically unlock those cells. – Gilligan Jul 30 '15 at 17:50