Disable a cell in excel using VB code

Solved/Closed
Jinto - Apr 12, 2012 at 04:43 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jan 28, 2016 at 11:10 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
May 1, 2012 at 08:28 AM
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
sneha1
Posts
1
Registration date
Friday June 13, 2014
Status
Member
Last seen
June 13, 2014

Jun 13, 2014 at 03:44 AM
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 16, 2014 at 11:19 AM
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
0
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Apr 13, 2012 at 10:54 PM
what is meant by "diable" cell.you mean protect so that no body can change the cell
1
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Apr 14, 2012 at 11:33 PM
1
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
0

Didn't find the answer you are looking for?

Ask a question
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Oct 11, 2012 at 09:20 AM
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
0
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.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Feb 3, 2014 at 10:30 AM
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
0
Raysgto
Posts
1
Registration date
Wednesday January 27, 2016
Status
Member
Last seen
January 27, 2016

Jan 27, 2016 at 08:49 AM
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jan 28, 2016 at 11:10 AM
Hi Ray,

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

Best regards,
Trowa
0