VBA Loop Through Data Validation Drop Down List

[RMBruno]

Hi everyone,

I am new to macros and when I tried to record a macro of myself clicking through data validation list to update certain formulas, it seems as though the action would not be recorded.

I have a drop down list in cell "K3" that has 5 options which are linked to cells "A3:A7"

I also have a drop down list in cell "I58" that has 4 options linked to cells "A59:A62"

For each drop down in cell "K3", I need to loop through each drop down in "I58"..

For example, I set the first drop down in "K3", then I click through each option in "I58", then click the second dropdown in "K3" and repeat running through each dropdown in "58" and so on..

Can someone please help me out? Thank you so much in advance and let me know if I need to clarify anything!

 

[rlv01]

If the dropdown uses a list, and the list is in the range you specified, then you can just loop through that range to obtain the list.

Sub demo1()
    Dim R As Range
    Dim VList As String

    For Each R In Range("A59:A62")                    'dropdown list for  cell I58
        VList = VList & R.Value & vbCr
    Next R

    MsgBox VList, , "Validation List for cell I58"
End Sub

 

[RMBruno]

If the dropdown uses a list, and the list is in the range you specified, then you can just loop through that range to obtain the list.

Sub demo1()
    Dim R As Range
    Dim VList As String

    For Each R In Range("A59:A62")                    'dropdown list for  cell I58
        VList = VList & R.Value & vbCr
    Next R

    MsgBox VList, , "Validation List for cell I58"
End Sub

Appreciate the reply. Will this code click through each option in the list for for cells I58 and K3?

Does not seem to be updating anything when I run this macro

 

[rlv01]

Appreciate the reply. Will this code click through each option in the list for for cells I58 and K3?

Does not seem to be updating anything when I run this macro

The word "click" implies some sort of keyboard or mouse interaction. The code does not do that. It will loop through the range of cells that store your validation list. In other words, it provides you with VBA access to the validation list. What you do with that is up to you. But the questions you are asking make me think that you are asking a completely different question.

 

[RMBruno]

The word "click" implies some sort of keyboard or mouse interaction. The code does not do that. It will loop through the range of cells that store your validation list. In other words, it provides you with VBA access to the validation list. What you do with that is up to you. But the questions you are asking make me think that you are asking a completely different question.

Understood. I may be mistaken.

In order to update a model, I need to click through 3 different hold periods (which are the in the data validation list). Once I set that cell to 3 years, the model will update, then I click the data validation to set it to 5 years, so the model returns will update for that hold period and so on.

When I tried to simply record myself clicking through these options, it did not seem to follow. Maybe there is not a way to run this in a macro.

I appreciate your help

 

[rlv01]

It sounds like you need to be using the worksheet change event subroutine, which can be used to detect when you have changed the dropdown selection and then take some action that you define in VBA code.