Build a dropdown on the fly

[JoeMajestee]

In my VBA, I often search for an expected value in row 1 of a worksheet. If I find more or less than expected, I would like to present the user with a dropdown containing what is in those rows so they can pick one. Is there a generic way to do this without creating a userform?

I like the idea of generic because the amount of data changes from worksheet to worksheet.

Thanks!

 

[Trevor G]

When you click a cell and use the right mouse button you get the Pick from List option which is a drop down showing what is available.

Have you considered using Validations as an alternative?

 

[JoeMajestee]

Not sure how that helps, but thanks.

For now, I'm opting to use application.inputbox, type:=8.

Set oFound = Rows(1).Find("Email Address")
If oFound Is Nothing Then Set oFound = Rows(1).Find("Con Email")
If oFound Is Nothing Then
    On Error Resume Next
    Set oFound = Application.InputBox( _
            "Select the cell in the top row of the column that contains your email address list.", _
            "Emailerator", Type:=8)
    If oFound Is Nothing Then End
End If

 

[Trevor G]

Pleased to read you have something to work with, but your message does ask about a drop down.


Although you might not want a userform it might be a better option, you can always call it from the worksheet you are on and then adjust the list to the range within the sheet, if that helps you!

 

[JoeMajestee]

Thanks Trevor. I pretty much expected the answer to be "no". I appreciate you're looking at it, though.