Add/clear drop down (data validation) based upon a cell's content

[dmj120]

I'm using the Macro recorder in an attempt to learn more, but need help. I'm trying to create/clear a drop down list based upon a cell's selection (from a separate list).

In cell C2 (a list), if "Hourly Rate" is selected a data validation list should populate C3, otherwise C3 should clear contents....making it a normal cell.

How do you add if C2=hourly rate then add drop down otherwise clear contents?

Sub testDropDown()
'
' testDropDown Macro
'

'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$N$17:$N$26"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With
End Sub
Sub testdeletedropdown()
'
' testdeletedropdown Macro
'

'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With
    Selection.ClearContents
End Sub

 

[DanteAmor]

Try this macro, to create or remove the dropdown list based on the value of cell C2::

Sub add_remove_DropDown()
  With Range("C3").Validation
    .Delete
    .Parent.Value = ""
    If Range("C2").Value = "Hourly Rate" Then
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=$N$17:$N$26"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = False
      .ShowError = True
    End If
  End With
End Sub

If you want it to work automatically when you modify cell C2, put the following code in the sheet events.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "C2" Then
    With Range("C3").Validation
      .Delete
      .Parent.Value = ""
      If Target.Value = "Hourly Rate" Then
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:="=$N$17:$N$26"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
      End If
    End With
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
----- --

 

[dmj120]

This is PERFECT, thank you!!!!!

Try this macro, to create or remove the dropdown list based on the value of cell C2::



If you want it to work automatically when you modify cell C2, put the following code in the sheet events.


SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
----- --

 

[dmj120]

So as not to duplicate a post....
Where/how would I add a default "10%" value for cell D5 to this perfect sheet event that DanteAmor provided?

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "D3" Then
    Range("D4,D6").Value = ""
    If Target.Value = "Hourly Rate" Then
      With Range("D4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:="=$N$18:$N$27"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
      End With
    Else
      Range("D4").Validation.Delete
    End If
  End If
End Sub

 

[DanteAmor]

Where/how would I add a default "10%" value for cell D5

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "D3" Then
    Range("D5").Value = 0.1
    Range("D4,D6").Value = ""
    If Target.Value = "Hourly Rate" Then
      With Range("D4").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
          Operator:=xlBetween, Formula1:="=$N$18:$N$27"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
      End With
    Else
      Range("D4").Validation.Delete
    End If
  End If
End Sub

 

[dmj120]

Thank you, Dante!! I tried adding Range,d5....0.1 in a few different spots, but why....

does this work here:

If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) = "D3" Then
Range("D5").Value = 0.1
Range("D4,D6").Value = ""
If Target.Value = "Hourly Rate" Then


but not here - above the IFs:
Range("D5").Value = 0.1
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) = "D3" Then

Range("D4,D6").Value = ""
If Target.Value = "Hourly Rate" Then

 

[DanteAmor]

but not here - above the IFs:

If you put it above the ifs, that is, immediately after the change event, it means that every time you modify any cell, the value of cell D5 will change to 10%, that's why I put it after the if ifs, that is, when you modify the cell in question D3.

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("D5").Value = 0.1
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "D3" Then

 

[dmj120]

...and now I know how to add color in code - rich(bb code)