Disable a cell in excel using VB code

Solved/Closed
Jinto - Apr 12, 2012 at 04:43 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 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 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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