Disable/Lock the cell on adjacent cell selection from the dropdown

[dimpy123]

Hi All,

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:

Please Login or Register  to view this content.

Thanks,
Dimpy

[jaslake]

Hi Dimpy

Welcome to the Forum!!!

Please wrap your Code in Code Tags...it's a Forum requirement. Highlight your Code and press the # icon.

Your Code will be something like this (not tested).

Please Login or Register  to view this content.

[dimpy123]

John,

Thank you very much for a quick reply. Your code works perfectly well for my scenario.

I will format the code from next time onwards, I am new to this forum, so was not aware.

Thanks,
Dimpy

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

[dimpy123]

John,

I am trying to extend the above functionality for another column (12-M). So, if I use and condition like
If Not (Target.Column = 9 and Target.Column = 12) Then Exit Sub and then I am using if-then condition for the data selection from the dropdown similar to the previous one. But, this time I can't disable the next column (i.e Column 13).

I tried writing another sub also didn't work. Please suggest a way to handle this.


Thanks,
Dimpy

[jaslake]

Hi Dimpy

Here's one way...

Please Login or Register  to view this content.

[dimpy123]

Got it. Thanks John. Really appreciate your help.

[jaslake]

You're welcome...glad I could help.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[dimpy123]

Hi John,

If I do that, the other cells are getting Locked because of the ActiveSheet.Protect statement. I am not able to enter content into them once the sheet gets protected after I enter data in column 2. I tried the following to unprotect the sheet for other column selections, but it doesn't work.

Please Login or Register  to view this content.

Thanks

[jaslake]

Hi John,

If I do that, the other cells are getting Locked because of the ActiveSheet.Protect statement. I am not able to enter content into them once the sheet gets protected after I enter data in column 2. I tried the following to unprotect the sheet for other column selections, but it doesn't work.

Please Login or Register  to view this content.

Thanks

We were discussing Column 9 and 12...and now Column 2. What is it you wish to do?

[dimpy123]

I am working on multiple excel sheets. So, this sheet has data in col 2 dropdown. On selecting other the adjacent Column gets enabled and that's good. But the protect sheet
Statement makes rest of the columns from columm 4 onwards read only.
We don't want the remaining col to be disabled after col3. Hope that clarifies the point. Basically same concept has to be implemented in multiple locations.
Please let me know.

[jaslake]

Hi Dimpy

It seems to me if you format these Cells as Unlocked the User should be able to enter Data into them..

We don't want the remaining col to be disabled after col3

[dimpy123]

Hi John,

I understand that. Based on that I tried:

I did Ctrl+A and then unprotect sheet, then I am able to enter the data until col-C (assuming macro is written for col-B and C). Past col-C the sheet gets protected again, so read-only then, I had to unprotect the sheet again and then format cells-> unblock the 'lock' checkbox then can enter the data.
This repeats for the 2nd row again. I think this approach might not be easy to understand for the users, so is there any way we can control col 2 and 3, so that sheet gets protected for col-3 only? (through code) and then the user can continue to enter data in remaining columns.
I can send you my excel spreadsheet (with the macro) to you for reference. Can I send you a private message with the file through the forum?

Thanks,
Dimpy

[jaslake]

Hi Dimpy

Please attach a sample workbook the Structure of which is the same as your actual workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Edit: Far as I know one cannot attach Files to a PM.

[dimpy123]

Hi John,

1. For Q36, I have figured out a way to unlock the cells after col-3, using the offset option as shown in the code below. However, is it a good approach?

Please remember to unprotect the sheet first manually then enter the data. If possible, can we use the code to unprotect the sheet work here?

The attached sheet shows the result sheet with the macro.

Please Login or Register  to view this content.

Thanks,
Dimpy

[jaslake]

H Dimpy

In what circumstances should a cell in Column C be unlocked...only when Column B is either blank or "Other"?

[dimpy123]

John,

I solved it. Thanks.

Dimpy