CODEWITHSUNDEEP
vbaexcel
Carliños Brown
Carliños Brown

Reputation: 1

Update drop down lists with the same choice clicking in the active list

I have some drop down lists in the same row. All of them have the same options in their list.

To set all the lists drop down with the same selection I have to click one by one in all of them.

I tried with Crtl+Intro but it doesn´t work. I also tried using in this the method keysen but this only works with regular cells, not with list drop down.

I tried with the event Worksheet SelectionChange and I had some results, but I have not managed to change the value of the list drop down that are not active. Here is some sample code:

If Target.Cells.Count > 1 And Target.Rows.Count = 1 Then
    Dim cell As Range
    For Each cell In Selection
        cell = ActiveCell.Value
    Next cell
End If

With this code it is necessary to have some value in the active cell before selected. If you change the value later doesn´t work.

I tried again with the event Worksheet Change but that was worst, because the different lines that I used always generated infinite cycles, even if I used close cycles to repeat the action only the number of cell selected.

So, I am looking for some code to change all the list drop down selected only changing the value in the active cell (active list drop down). Clicking once and change all of them with the same value.

Upvotes: 0

Views: 69

Answers (1)

Alex P
Alex P

Reputation: 12489

Assume I have three drop down menus in A1, B1, and C1.

This code sets all values the same based on changing the drop down in A1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dd1 As Range, dd2 As Range, dd3 As Range

    Set dd1 = Range("A1")
    Set dd2 = Range("B1")
    Set dd3 = Range("C1")

    If Not Intersect(Target, dd1) Is Nothing Then
        dd2 = dd1.Validation.Parent
        dd3 = dd1.Validation.Parent
    End If
End Sub

Upvotes: 1

Related Questions