Changing Value of one drop down list when user makes a change in another drop down list

1
votes

In Excel, I would like the displayed/selected value of a Drop Down List to change when a user selects an item from another Drop Down List. (I can already change the options in the list)

Background:

List1 is a drop-down list with the following available entries:

Product1                     
Product2

List2 is a drop-down list with the following available entries (these are payment periods):

6  (available for Product1 and Product2)
10 (available for Product1 and Product2)
3  (available for Product2)
16 (available for Product2)
20 (available for Product2)

So far, I have managed to update the available drop-down entries in List2 when the selected item in List1 changes. But it is only the drop-down entries which change; the actual currently visible content of List2 does not change.

Issue:

This is not yet what I would like: As soon as the selected item in List1 changes, List2 should immediately display "Please Select" (in addition to List2's available drop-down entries being updated), so a user is aware that a selection must be made in List2.

2
vbaexcelexcel-2010
I believ you will be using some VBA and looking at cell change. I remember vaguely doing this once upon a time and you wind up using something like a cell intersection command in VBA to watch for a cell change. and then there was something else about using an event section like spreadsheet change. When either of these things happens, have VBA set the value of your second list to "please choose". Not an exact answer but might lead you to some ideas what you can search for. – Forward Ed
The only non-VBA option I can suggest is to have conditional formatting that turns the List2 cell a funny-colour if it contains a value that is not valid for that List1 value (i.e. using =Not(IsError(Match(..))) in a Conditional Format Formula) – Chronocidal

2 Answers

1
votes

Make use of the Worksheet_Change event and use Intersect to trigger only on specific cells.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("List1")) Is Nothing Then
        Application.EnableEvents = False 'prevent triggering another change event
        Range("List2").Value = "Please Select …"
        Application.EnableEvents = True
    End If
End Sub

Note Range("List1") should be changed to reference the cell of your List1 DropDown. And Range("List2") accordingly.

0
votes

This code will update your dropwdown list according to the value you selected in your List1

    Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim MyCell As Range

Set MyCell = Range("A1") 'Change A1 by cell where you have List1 dropwdown list

If Target.Address <> MyCell.Address Then
    'we do nothing
    Set MyCell = Nothing
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Else
    'we modify List2 dropdown list
    Dim MyOption As String
    MyOption = MyCell.Value
    Set MyCell = Nothing
    Set MyCell = Range("B1") 'Change B2 by Cell where you have List2 dropwdown list

    'now 'We use Select Case because you can add all cases you want, just in case in Future you have, for example, Product3
    Select Case MyOption
        Case "Product1"
            With MyCell.Validation
                .Delete
                MyCell.Value = "Please Select"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="6,10"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        Case "Product2"
            With MyCell.Validation
                .Delete
                MyCell.Value = "Please Select"
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="6,10,3,16,20"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
            Case Else
                MsgBox "Update code with new option"
    End Select
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

It works for me:

enter image description here

UPDATED CODE: Added the "Please Select" part, after issue was cleared.