Making a cell noneditable based on dropdown value in another cell

[raghuisnow]

I want to make a cell with drop down (validation) non editable based on the value of drop down in another cell.

I tried my code but seems not working correctly. I try to protect the cell and sheet which locks the sheet totally. It seems not working . I need help please help me. its urgent. I know programming in vb and using that knowledge to try my best.

Column G have drop down with values (Entered,Not Entered,Blocked)
Column I have drop down with some values
On selection of Blocked or Not Entered in Column G (any row) the corresponding row in Column I should be non editable.

here is the code i tried.

[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m As Integer
Dim ColName As String
Dim RowNumb As Integer
If Target.Column = 7 And Target.Value <> "" Then
Select Case Target.Value
Case "Blocked"
RowNumb = Target.Row
Do While (Target.Column + 1) > 0
m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
Exit Do
Loop
Case "Not Tested"


End Select
Range(ColName & RowNumb & ":" & ColName & RowNumb).Select
With Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
Range(ColName & RowNumb & ":" & ColName & RowNumb).Locked = True
Worksheets("Test Case Matrix").Protect
End If
End Sub
[/VBA]

[GTO]

I want to make a cell with drop down (validation) non editable based on the value of drop down in another cell.

I try to protect the cell and sheet which locks the sheet totally. It seems not working .

Column G have drop down with values (Entered,Not Entered,Blocked)
Column I have drop down with some values
On selection of Blocked or Not Entered in Column G (any row) the corresponding row in Column I should be non editable.

Greetings raghuisnow,

If you are trying to "lock" or protect the corresping cell in Col I, whenever a cell in Col G contains "Blocked" or "Not Entered", what is the extra code for? As far as I can see, the Do Loop cannot loop, as it hits an Exit first go-around.

If the 'extra' testing is not needed, I would think the Change event would be handier than the SelectionChange. Also, you'll need to first unlock all the cells that the user should be able to edit and protect the sheet. Then when a change is made, the code would unprotect sheet, lock correct cell in Col I, and reprotect sheet.

Does that help?

Mark

[raghuisnow]

For the above question I want it to atleast have drop down removed and cell gryed out. I tried and did bt it is not consistent. when I change in any cell it generate error. The reason I wrote the code so lengthy is that because Im not expert in VBA programming. I know some VB programming and managing with it. Please help anyone. Here is the my updated code.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim m As Integer
Dim ColName As String
Dim RowNumb As Integer
If Target.Column = 7 And (Target.Value = "Blocked" Or Target.Value = "Not Tested") And Target.Value <> "" Then
Select Case Target.Value
Case "Blocked"
RowNumb = Target.Row
Do While (Target.Column + 1) > 0
m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
Exit Do
Loop
Case "Not Tested"
RowNumb = Target.Row
Do While (Target.Column + 1) > 0
m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
Exit Do
Loop

End Select
Range(ColName & RowNumb & ":" & ColName & RowNumb).Select
With Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
End With
'Range(ColName & RowNumb & ":" & ColName & RowNumb).Validation.Formula1 = ""
Range(ColName & RowNumb & ":" & ColName & RowNumb).Validation.InCellDropdown = False
End If
End Sub
[/VBA]

[GTO]

[vba]
Do While (Target.Column + 1) > 0
m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
Exit Do
Loop
[/vba]

Hi again,

Okay, with the Exit Do in there, it cannot loop. As this part is still confusing to me, this example is very simple and just checks for the values in Col G.

In a blank wb or a throwaway copy of your wb, unlock cells G2:G20 and I2:I20.

In the Worksheet's Module:
[vba]
Option Explicit
Const PWORD As String = "MyPassword" '<--- Change to password you have the sheet protected with.
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("G2:G20")) Is Nothing _
And Not Target.Count > 1 Then
If Target.Value = "Blocked" _
Or Target.Value = "Not Entered" Then
Me.Unprotect PWORD
With Target.Offset(, 2)
.Locked = True
.Interior.ColorIndex = 48
.Interior.Pattern = xlSolid
End With
Me.Protect PWORD, , , , True
ElseIf Target.Value = "Entered" Then
Me.Unprotect PWORD
With Target.Offset(, 2)
.Locked = False
.Interior.ColorIndex = xlNone
End With
Me.Protect PWORD, , , , True
End If
End If
[/vba]

If that does anything close to what you want, we can go from there :-)

Mark

[Simon Lloyd]

Raghuisnow, please wrap any further code in VBA code tags by highlighting your code and clicking the green VBA button.

Maybe you can adapt my Protect Cells Without Protecting Worksheet kb entry, of course you would have to change the userform code but it may help you http://www.vbaexpress.com/kb/getarticle.php?kb_id=931

[raghuisnow]

First thank you for all helping me out with wonderful code and suggestions. I came to realization that protecting cell / sheet will not help and I have to change the idea to just make other changes to the some cells based on value selected in say cell G.

In cell G if i select value Not Entered or Blocked then Columns I,J,L and M should be greyed out so that any text entered will not be visible and the drop down in the column I,J and L should be removed.

I can avoid protecting cell/sheet and all lot of confusion around it. I need to change my plan but this option i meantioned above should be provided it can be implemented. Please help me.

[raghuisnow]

Experts any help is appreciable.

[GTO]

...I came to realization that protecting cell / sheet will not help and I have to change the idea to just make other changes to the some cells based on value selected in say cell G...

Greetings raghuisnow,

Okay, so we are changing gears here. I think that we may need a bit more specific/detailed info to get this right. For example:

...In cell G if i select value Not Entered or Blocked then Columns I,J,L and M should be greyed out so that any text entered will not be visible and the drop down in the column I,J and L should be removed.

Eh? While I'm confident you mean just to grey out the corresponding cells in the same row, are we also wanting to change the text to the same color as the cell's interior, so that it is hidden?

After a val of 'Blocked' is entered in let's say G3, and we change colors in I/J/L/M (row 3) and remove validation, what happens if the user later changes G3 back to 'Entered'?

Also - without sheet protection in place, regardless of whether the contents of the greyed cells being obvious, there is now nothing to prevent the user from deleting/changing the values in these cells.

Maybe posting an example workbook (devoid of any personal/private/company info), that shows a before and after would help.

Mark

[p45cal]

While waiting for clarification on what's wanted could I point out that GTO's code can be shortened somewhat. GTO did set the UserInterfaceOnly option to True when protecting the sheet, and so repeated protecting/unprotecting the sheet to lock/unlock cells etc. is not needed. This works:[vba]Option Explicit
Const PWORD As String = "MyPassword" '<--- Change to password you have the sheet protected with.
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect Password:=PWORD, UserInterfaceOnly:=True 'this only needs to be done once, perhaps elsewhere eg. on saving the workbook, or activating this sheet.
If Not Application.Intersect(Target, Range("G2:G20")) Is Nothing And Target.Count = 1 Then
Select Case Target.Value
Case "Blocked", "Not Entered"
With Target.Offset(, 2)
.Locked = True
.Interior.ColorIndex = 48
.Interior.Pattern = xlSolid
End With
Case "Entered"
With Target.Offset(, 2)
.Locked = False
.Interior.ColorIndex = xlNone
End With
End Select
End If
End Sub[/vba](I've used Select Case only 'cos I find ElseIfs difficult..)

ps. The bit [vba] m = (Target.Column + 1) Mod 26
ColName = Chr(65 + m) + ColName
[/vba]seems to give the column letter header of the cell 2 cells to the right. It breaks down once you get beyond Z. You shouldn't need to do this in the code anyway, but a more reliable way is:[vba]ColName = Split(Target.Offset(, 2).Address, "$")(1)[/vba]