i have the code below running a dropdown menu. when certain parameters
change the value last selected for the cell might no longer be available. my
issue is that i need to reset the default value if the previously selected
value is no longer available.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") < "0" Then List = List & "," & Cells(t, "H")
Next
With Range("E14").Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub

Try explain ur problem in simpel english - im from Denmark


"Matt" skrev:

i have the code below running a dropdown menu. when certain parameters
change the value last selected for the cell might no longer be available. my
issue is that i need to reset the default value if the previously selected
value is no longer available.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") < "0" Then List = List & "," & Cells(t, "H")
Next
With Range("E14").Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub

not sure what u mean ?


"Matt" skrev:

i have the code below running a dropdown menu. when certain parameters
change the value last selected for the cell might no longer be available. my
issue is that i need to reset the default value if the previously selected
value is no longer available.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") < "0" Then List = List & "," & Cells(t, "H")
Next
With Range("E14").Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub

You could check the contents of cell E14 against the list of values:

With Range("E14")
With .Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
If InStr(1, List, .Value) = 0 Then
.ClearContents
End If
End With


Matt wrote:
i have the code below running a dropdown menu. when certain parameters
change the value last selected for the cell might no longer be available. my
issue is that i need to reset the default value if the previously selected
value is no longer available.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim t, List
For t = 18 To 24
If Cells(t, "H") < "0" Then List = List & "," & Cells(t, "H")
Next
With Range("E14").Validation
.Delete
.Add xlValidateList, Formula1:=List
.InCellDropdown = True
End With
End Sub


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html