Dealing with dates

[Jackeb]

Hi,

I have created a bit of an aplication in excel. In the process I have managed to get y head around a lot of vba, but I have come across a , hopefully, small problem and I am looking for one of thoes neat simple and "I knew that" kinda answers.

I have a combobox that is populated from a list of dates in a worksheet, this list linked to the current date and as such I use the .rowsource property of the combobox and the lines:

DB.Activate
Range("C2").Select
Selection.End(xlDown).Select
dlr = ActiveCell.Row

Me.tldate.RowSource = "C2:C" & dlr

where the dates are in col 'C' and db is the sheet.

My problem is that when I select the date in the drop down box, they are displayed as dates, the number that is returned to the .text value of the combobox is not in a date format, it is in a genberal number format.

I have used the combobox1_change event to format that returned number but this causes problems elsewhere in my program. I am hoping that there is a property in the combobox that I can specify this?

Any thoughts?

[smozgur]

Hi Jackeb,

Sorry i am a late .

Private Sub tldate_Change()
tldate.Text = Format(Me.tldate.Text, "mm.dd.yyyy")
End Sub

I think this would work for you.
regards

_________________
Oz ~ TheWordExpert

[ This Message was edited by: smozgur on 2002-03-30 13:56 ]

[Dave Hawley]

Hi Jack

Just a extra to smozgur's suggestion.

Private Sub ComboBox1_Change()
Dim strDate As String

If ComboBox1.ListIndex > -1 Then
strDate = Format(ComboBox1, "dd/mm/yyyy")
ComboBox1 = strDate
End If

End Sub


This will prevent the code from running while they are typing into the ComboBox.

A couple of tips about your ComboBox RowSource, you could use:

ComboBox1.RowSource = Range("C2", Range("C2").End(xlDown)).Address

OR

ComboBox1.RowSource = Range("C2", Range("C65536").End(xlUp)).Address


But the ideal solution for this is a Dynamic range.

http://www.ozgrid.com/Excel/DynamicRanges.htm