none
How to populate Worksheet Dropdown with Transposed list RRS feed

  • Question

  • I have column headers in a table that are dates. I wish to populate a worksheet dropdown (Forms 2.0 control) with the dates, preferably without creating a transposed list of dates. Is this possible? Thanks in advance...
    Wednesday, May 8, 2019 9:37 AM

All replies

  • As far as I know, that is not possible. You could create the transposed range on a hidden worksheet.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 8, 2019 10:31 AM
  • Thanks Hans, that's my least preferred option. I may go for in-cell Data Validation, which works, but I prefer to use a Forms 2.0 Control (or even Active-X).
    Wednesday, May 8, 2019 10:55 AM
  • If you use an ActiveX combo box you can use code like this:

    Sheet1.ComboBox1 = Application.Transpose(Sheet1.Range("B1:J1"))

    but that populates the list with the numeric values of the dates - you'd see 43466 instead of 1/1/2019.

    An alternative could be

    Sub Test()
        Dim arr(1 To 9) As String
        Dim c As Long
        For c = 1 To 9
            arr(c) = Cells(1, c + 1).Text
        Next c
        Sheet1.ComboBox1.List = arr
    End Sub

    Modify as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 8, 2019 11:36 AM
  • Thanks, Hans. Actually, the dates are in an Excel Table, so the dates are already stored as text. I'm currently using the transposed list in a Forms 2.0 dropdown. I may stick with this, or if I find the time, I might try out the ActiveX dropdown. Dankje wel…
    Thursday, May 9, 2019 9:02 AM