Disable a cell in excel using VB codeSolved/Closed

Question

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



Configuration: Windows 7 / Safari 535.19

TrowaD · Accepted Answer

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

venkat1926 · Answer

what is meant by "diable" cell.you mean protect so that no body can change the cell

venkat1926 · Answer

study this webpage

https://support.microsoft.com/en-gb/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a?ui=en-us&rs=en-gb&ad=gb

KP · Answer

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

TrowaD · Answer

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

Nani · Answer

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.

TrowaD · Answer

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 ThenRange("B1").Value = 99End 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 ThenRange("B1").Value = 99End IfEnd Sub

2) Using 1 line only.
Sub test()If Range("A1").Value = 1 Then Range("B1").Value = 99End Sub

Best regards,
Trowa

Disable a cell in excel using VB code

7 responses

Similar discussions

Subscribe To Our Newsletter!