Disable Cell with popup message

[mhasham]

Hello,

I am attempting to disable a cell (data validation list) based on an entry of another cell (data validation list).

For example:
IN CEll H5 - If user selects 0 from the data validation drop down then CELL H6 becomes disabled (greyed out preferebaly) and it sends user to CELL H7 automatically



Previous POSTS (mumps posted the following in 2012, not sure if it can be leverged in any way)
Sub Test() ActiveSheet.Unprotect If Range("A1") = "Solicitud" Then Range("B1").Locked = False Else Range("B1").Locked = True End If ActiveSheet.ProtectEnd Sub</PRE>

Any help would be greatly appreciated.

 

[JoeMo]

Put this code in the worksheet that contains your validation list.

To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
Range("H6").Interior.Color = xlNone
If Not Intersect(Target, Range("H5")) Is Nothing Then
    If Target.Value = 0 Then
        Range("H6").Interior.Color = RGB(150, 150, 150)
        Target.Offset(2, 0).Select
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("H6")) Is Nothing Then
    If Range("H5").Value = 0 Then
        Application.EnableEvents = False
        Range("H7").Select
        Application.EnableEvents = True
    End If
End If
End Sub

 

[mhasham]

Works perfectly, thanks JoeMo............One more question, If I wanted to select a range instead of single cell, is it doable by changing the quotations "H5" to lets say "H5:H8"

 

[JoeMo]

Works perfectly, thanks JoeMo............One more question, If I wanted to select a range instead of single cell, is it doable by changing the quotations "H5" to lets say "H5:H8"

What do you want to happen if you select a range - say H5:H8?

 

[mhasham]

Sorry let me make it more clear.


So similar concept to the first code, except that i would like to disable a range lets say H6:H9

 

[JoeMo]

Sorry let me make it more clear.


So similar concept to the first code, except that i would like to disable a range lets say H6:H9

Then if H5 is still the target, you can change H6 to H6:H9 and H7 to H10 wherever they appear in the two subs.