Disable a cell in excel using VB code [Solved/Closed]

Report
-
TrowaD
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
-
Hello,

I am looking for a VB code to disable a cell based on the value of another cell in excel.

Example:- Need to disable Cell B1/C1/D1 based on the value selected from the dropdown in cell A1.
Suppose value selected in cell A1 =xyz, then disable cell B1.But keep the other cell enabled.
If value selected in cell A1= abc then disable cell C1.But keep the other cell enabled.

Thanks in advance for your answer!
Jinto



7 replies

Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Jinto,

Once you have unblocked all cells (Ctrl+a to select all cells, Ctrl+1 to goto cell properties, final tab protection).

You can try this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A1") Then Exit Sub

ActiveSheet.Unprotect

If Range("A1").Value = "xyz" Then
Range("B1").Locked = True
Range("C1").Locked = False
Range("D1").Locked = False
End If

If Range("A1").Value = "abc" Then
Range("B1").Locked = False
Range("C1").Locked = True
Range("D1").Locked = False
End If

If Range("A1").Value = 123 Then
Range("B1").Locked = False
Range("C1").Locked = False
Range("D1").Locked = True
End If

If Range("A1").Value = "" Then
Range("B1").Locked = False
Range("C1").Locked = False
Range("D1").Locked = False
End If

ActiveSheet.Protect

End Sub

Best regards,
Trowa
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4003 users have said thank you to us this month

sneha1
Posts
1
Registration date
Friday June 13, 2014
Status
Member
Last seen
June 13, 2014

Hi Trowa,
I have a problem. I modified the code according to my needs but it doesn't seem to work. Is there a specific place where I should put the code so that it will work. I am new with working with macros. So I am looking forward to your help.

Regards
Sneha
TrowaD
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Sneha,

To correctly place this code is to right-click the sheets tab of which you want to apply the code to and select view code. Then paste the code in the big white field that appears.

Let me know if I can assist further.

Best regards,
Trowa
Hi Jinto,

use this simple IF formula
=IF(C3="No","NA"," ")
if C3 = NO or something else then your value will be NA or something that you define
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
what is meant by "diable" cell.you mean protect so that no body can change the cell
Dear Venkat,

'Yes' I need to protect the cell from change, based on the value selected in another cell- as ssen in my comment above.
Thanks for showing interest in my query
Jinto
Trowa,

Your solution is good, but when I try to change the value in the dropdown menu (A1), it tells me the whole worksheet is protected. I can get around this by manually unprotecting the whole worksheet, but is there a way to code it into the macro so that the value in the dropdown menu can be changed with the macro running again after that?

Thank you,
KP
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi KP,

I think you missed the first step:
"Once you have unblocked all cells (Ctrl+a to select all cells, Ctrl+1 to goto cell properties, final tab protection). "

By unblocking all cells, even if the sheet is protected, you can still input data. Except for the specific values and ranges mentioned in the code.

Can you get it to work now?

Best regards,
Trowa
Dear Trowa,

When I am using your formula to disable the cell, it is showing a error message like "compile error: Block if without end if"

What does it mean.
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Nani,

That means that you started an "if" statement using more then 1 line without the "end if" line at the end.

Here is an example to clarify:
Sub test()
If Range("A1").Value = 1 Then
Range("B1").Value = 99
End Sub

Will produce the error you mentioned.

You can solve this 2 ways.
1) add "end if" to close the "if block".
Sub test()
If Range("A1").Value = 1 Then
Range("B1").Value = 99
End If
End Sub

2) Using 1 line only.
Sub test()
If Range("A1").Value = 1 Then Range("B1").Value = 99
End Sub

Best regards,
Trowa
Raysgto
Posts
1
Registration date
Wednesday January 27, 2016
Status
Member
Last seen
January 27, 2016

Hi Trowa,

If we have to define a Range of Cells say from A1:A100, how can we work the VBA script, with the locked cell also going from say B1:B100.

Regards,
Ray
TrowaD
Posts
2599
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
March 24, 2020
401
Hi Ray,

Could you explain in greater detail, as I am lost as to what you want to achieve.

Best regards,
Trowa