Trigger data validation from worksheet change event

[terriertrip]

Just like the title says. I've read numerous posts on how to trigger a worksheet change event upon data validation, but am having a bit of difficulty trying to find something going the other way...in relation to creating a data validation cell when a value is placed within an adjacent cell within a table.

e.g. user places value in cell A (to lastrow) which triggers cell B (to lastrow) to a data validation dropdown. I've set A to be a dynamic named range.


Data validation example:

Private Sub Worksheet_Change(ByVal Target As Range)

' dynamic named range "Test" is range "A" that user inputs a value
If Not Intersect(Target, Range("Test")) Is Nothing Then
        On Error Resume Next
        Application.EnableEvents = False
            Call Valid
            Sheets(5).UsedRange.Columns.AutoFit
        Application.EnableEvents = True
        On Error GoTo 0
        End If

End Sub

Sub Valid()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim range1 As Range, rng As Range
Set ws2 = ThisWorkbook.Worksheets("Employee_Contacts")
Set ws1 = ThisWorkbook.Worksheets("Sheet3")
Set range1 = ws1.Range("C2")
' dynamic named range of "Valid" is range B that creates a data validation list
Set rng = ws2.Range("Valid").Rows
With rng.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & ws1.Name & "'!" & range1.Address
End With
End Sub

Much appreciated

[dmcgov]

so i have a few questions for you.

i have a sample workbook that is configured to add a validation dropdown to column B if Column A value is greater than 0.

but that is all it does, so you type a 1 in a cell in column A and in the same row but column B it displays a dropdown list.

is that what you wanted? is yes, then i will upload the workbook.

[terriertrip]

Thank. I'm sure that would work. If you want to just send me the code, that would be ok too. The value (A) is string, but since the validation only occurs when a value is entered into A, I could equate another range with an IFTHEN >0 statement when A is not null and configure the validation to that working range.

[dmcgov]

ok, put this in the sheet that you want to monitor:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("a:a")

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        processCells Application.Intersect(KeyCells, Target)
    End If
End Sub

and put this in module 1:

Sub processCells(ByVal Target As Range)

    For Each cell In Target.Cells
        Select Case cell.Value
            Case Is > 0
                With Range("B" & Target.Row).Select
                    With Selection.Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=Sheet2!$A$1:$A$5"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = ""
                        .ErrorMessage = ""
                        .ShowInput = True
                        .ShowError = True
                    End With
                End With
            Case Is < 0
                MsgBox "Process negative number"
                'do nothing
            Case Is = 0
                MsgBox "Process zero"
            Case Else
                MsgBox "Case Else"
        End Select
    Next cell

End Sub

also make sheet2 and put in A1 to A5 "1", "2", "3", "4", "5". That is what the validation list references.

ill upload the workbook too just in case you need it.

[terriertrip]

Thanks dmcgov. Very helpful.

[dmcgov]

if this fixes your issue please mark the topic as solved (Thread Tools->Mark as Solved) and then click on "Add Reputation", that is always helpful for me.

[terriertrip]

I thought I had clicked, but let me know if it didn't go through (if you can). By simply removing the cases, it works when a any value (numerical, string) is entered. The validation remains if the cell is null or entire row is deleted however.

[dmcgov]

I checked and no apparently the click on Add Reputation did not go through, not the end of the world.

post what you have and i will tweak it to clear out that cell.

[terriertrip]

Yeah, boards been timing out and wanting me to keep logging back in every minute so not sure what the issue maybe. I'll give it a go again. Least I could do for your help.

Sub processCells(ByVal Target As Range)

    For Each cell In Target.Cells
        With Range("B" & Target.Row).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Sheet2!$A$1"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            MsgBox "New employee is probationary."
        End With
    Next cell
End Sub