Stephen Byrom
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.
How can I get the combo box to show dates as dd/mmm.
Thanks in advance
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
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
Actually, I don't think that will work. The value copied into the array is an unformatted date.
Kevin
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("ShiptTo y")
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(vaD ata) 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) ).ClearCon tents
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
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("ShiptTo
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)
ReDim vaDataFormatted(LBound(vaD
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)
End With
'display the unique values in vaData in the combo box already in existence on the worksheet.
With WS2.OLEObjects("ComboBox1"
.Clear
.List = vaDataFormatted
.ListIndex = -1
End With
End Sub
Kevin
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.
But thanks for trying.
I also tried to sort the column xldescending but couldn't get that to work either.
ASKER
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")
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(.Ran ge(.Range( "AA2"), .Range("AA1000").End(xlUp) ).Value)
Kevin
vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)
to:
vaData = Application.Transpose(.Ran
Kevin
ASKER
I managed to get this to work Kevin,
Is there a way to sort the data descending before it fills the combo box?
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
I changed the "vaData" to "rnData" (line 26)Is there a way to sort the data descending before it fills the combo box?
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
here goes,
combo.xlsm
combo.xlsm
ASKER
Brilliant!
Thanks for your time and expertise.
Thanks for your time and expertise.
vaData = .Range(.Range("AA2"), .Range("AA1000").End(xlUp)
this line:
.Range(.Range("AA2"), .Range("AA1000").End(xlUp)
Kevin