Link to home
Start Free TrialLog in
Avatar of Stephen Byrom
Stephen ByromFlag for Ireland

asked on

Date format in VBA

HI,
I have this code that I copied from Microsoft's help pages to populate an activeX combo box on a worksheet.
Private Sub Worksheet_Activate()
    Dim wbBook As Workbook
    Dim WS As Worksheet
    Dim WS2 As Worksheet
    Dim rnData As Range

    'Variant to contain the data to be placed in the combo box.
    Dim vaData As Variant

    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set WS = wbBook.Worksheets("ShiptToy")
    Set WS2 = wbBook.Worksheets("Toyota")

    'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
    With WS
        Set rnData = .Range(.Range("F2"), .Range("F1000").End(xlUp))
        rnData.AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=.Range("AA1"), _
                          Unique:=True
        'store the unique values in vaData
        vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value
        'clean up the contents of the temporary data storage
        .Range(.Range("AA1"), .Range("AA1000").End(xlUp)).ClearContents
    End With

    'display the unique values in vaData in the combo box already in existence on the worksheet.
    With WS2.OLEObjects("ComboBox1").Object
        .Clear
        .List = vaData
        .ListIndex = -1
    End With
End Sub

Open in new window

Problem is that the column I'm filling the combo box with is full of dates, and although the code does the job, it's in the american format (m/d/y)
How can I get the combo box to show dates as  dd/mmm.

Thanks in advance
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Insert before this line:

        vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value

this line:

        .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).NumberFormat = "dd/mmm"

Kevin
Actually, I don't think that will work. The value copied into the array is an unformatted date.

Kevin
Try this:

Private Sub Worksheet_Activate()
    Dim wbBook As Workbook
    Dim WS As Worksheet
    Dim WS2 As Worksheet
    Dim rnData As Range
    Dim Index As Long

    'Variant to contain the data to be placed in the combo box.
    Dim vaData As Variant
    Dim vaDataFormatted As Variant

    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set WS = wbBook.Worksheets("ShiptToy")
    Set WS2 = wbBook.Worksheets("Toyota")

    'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
    With WS
        Set rnData = .Range(.Range("F2"), .Range("F1000").End(xlUp))
        rnData.AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=.Range("AA1"), _
                          Unique:=True
        'store the unique values in vaData
        vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value
        ReDim vaDataFormatted(LBound(vaData) To UBound(vaData))
        For Index = LBound(vaDataFormatted) To UBound(vaDataFormatted)
            vaDataFormatted(Index) = Format(vaData(Index), "dd/mmm")
        Next Index
        'clean up the contents of the temporary data storage
        .Range(.Range("AA1"), .Range("AA1000").End(xlUp)).ClearContents
    End With

    'display the unique values in vaData in the combo box already in existence on the worksheet.
    With WS2.OLEObjects("ComboBox1").Object
        .Clear
        .List = vaDataFormatted
        .ListIndex = -1
    End With
End Sub

Kevin
Avatar of Stephen Byrom

ASKER

You're right, didn't work.
But thanks for trying.
I also tried to sort the column xldescending but couldn't get that to work either.
Oops posts musta crossed.
Anyway, thanks again for your time, but I get an error at this line

            vaDataFormatted(Index) = Format(vaData(Index), "dd/mmm")
Change this line:

       vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value

to:

       vaData = Application.Transpose(.Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value)

Kevin
I managed to get this to work Kevin,
    Dim wbBook As Workbook
    Dim WS As Worksheet
    Dim WS2 As Worksheet
    Dim rnData As Range
    Dim Index As Long

    'Variant to contain the data to be placed in the combo box.
    Dim vaData As Variant
    Dim vaDataFormatted As Variant

    'Initialize the Excel objects
    Set wbBook = ThisWorkbook
    Set WS = wbBook.Worksheets("ShiptToy")
    Set WS2 = wbBook.Worksheets("Toyota")

    'Set the range equal to the data, and then (temporarily) copy the unique values of that data to the L column.
    With WS
        Set rnData = .Range(.Range("F2"), .Range("F1000").End(xlUp))
        rnData.AdvancedFilter Action:=xlFilterCopy, _
                          CopyToRange:=.Range("AA1"), _
                          Unique:=True
        'store the unique values in vaData
        vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)).Value
        ReDim vaDataFormatted(LBound(vaData) To UBound(vaData))
        For Index = LBound(vaDataFormatted) To UBound(vaDataFormatted)
            vaDataFormatted(Index) = Format(rnData(Index), "dd/mmm")
        Next Index
        'clean up the contents of the temporary data storage
        .Range(.Range("AA1"), .Range("AA1000").End(xlUp)).ClearContents
    End With

    'display the unique values in vaData in the combo box already in existence on the worksheet.
    With WS2.OLEObjects("ComboBox1").Object
        .Clear
        .List = vaDataFormatted
        .ListIndex = -1
    End With
End Sub

Open in new window

I changed the "vaData" to "rnData" (line 26)
Is there a way to sort the data descending before it fills the combo box?
Nope, I was wrong.
not filling the combo box with all the data.
I'll trim down the workbook and upload it to give you a better idea of what I'm trying to do.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
here goes,
combo.xlsm
Brilliant!
Thanks for your time and expertise.