Excel VBA - Worksheet_Change drop-down list

Question

Hi Stackoverflow community,

I want to add a Data Validation drop-down list if a condition is met.

Using Excel VBA, I want to add the drop-down list to populate in Column N which is my Account Status, whenever the word "Final Recon" appears in Column C which is my Valuation Status but only populate if the word "Final Recon" appears in Column C.

Using the Excel VBA - Private Sub Worksheet_Change(ByVal Target as Range):

If "Final Recon" appears in Column C then populate the drop-down list with the following status: "Final" or "Under Review", otherwise don't populate the drop-down list.

I can use the non-VBA validation list but in this type of valuation review I need to use the VBA version.

This is what I have so far and I am pretyy much stuck. I have "Final Recon" in column C , if final recon appears in Column C then populate the drop-down list with the option of "Final" and "Under Review".

But for some reason the drop-dwon list is not populating in Column N when Final Recon appears in Column C.

Thank you in advance for any help or solution you may have.

 Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Then

    ThisRow = Target.Row

    If Target.Value = "Final Recon" Then

    With Selection.Validation
        .Range ("N" & ThisRow)


.Delete
.Add Type:=xlValidateList, Formula1:="=Final", Formula2:="=Under Review"
 End With

    Else
        Range("N" & ThisRow) = ""
    End If
  End If
 End Sub

cemg cemg · Accepted Answer · 2016-02-08T17:24:18

I was able to figure it out. the only problem I am having is for the drop-down not to show up if "Final Recon" is not in Column "C".

   On Error Resume Next

 If Target.Column = 3 Then

   If Target.Validation.Type = "Final Recon" Then

    Application.EnableEvents = False

     ''get the data drop-down validation list

    Target.Offset(0, 11).Select

    With Selection.Validation


  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop
     Operator:=xlBetween, _                      
  Formula1:="Final", Formula2:="Under Review"

  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
  .ShowError = True

  End With

 Else
  Target.Offset(0, 11).Value = ""

   End If
 End If

 exitHandler:
   Application.EnableEvents = True
   Exit Sub

Excel VBA - Worksheet_Change drop-down list

1 Answers