text display

[av8tordude]

My listbox has 2 columns

first column has the Months (January, February, March, etc.)
second column has the effective dates (01/01/2011, 02,01,2011,03/01/2011, etc.) in that format.

How can I display the month and effective date for January & 01/01/2011 in a textbox like.... Jan2011

[Kenneth Hobs]

Basically, use CDate() to convert the actual date string to a date and then Format() to format to the date format that you want.

[av8tordude]

Hi Ken, I'm completely confuse by your suggestion. Could you give me an example? Thank you for responding.

[Kenneth Hobs]

[VBA]Private Sub ListBox1_AfterUpdate()
On Error Resume Next
TextBox1.Value = Format(CDate(ListBox1.Value), "mmmyyyy")
End Sub[/VBA]

[av8tordude]

Ok...I understand. Unfortunately I'm aware of this method, but it would not work with this listbox. The listbox is format with two columns. The left column has months. The right column has the dates

January.....|... 01/01/2011 ....Display in textbox as: Jan2011
February...|... 02/01/2011 .....Display in textbox as: Feb2011
March.......|... 03/01/2011 ..... Display in textbox as: Mar2011

[Kenneth Hobs]

It does work if you set:
[VBA]ListBox1.BoundColumn =2[/VBA]
Even if you don't do that, you can still make it work with this method:
[VBA]Private Sub ListBox1_AfterUpdate()
On Error Resume Next
TextBox1.Value = Format(CDate(ListBox1.List(ListBox1.ListIndex, 1)), "mmmyyyy")
End Sub [/VBA]

[av8tordude]

Ok...Got it to work. Thank you

[sukumar.vb]

Thanks Ken, this was great solution.

[Kenneth Hobs]

Thanks for the flowers. For others that might like to see this in action, add to userform1, listbox1, textbox1 and commandbutton1.

[VBA]Private Sub CommandButton1_Click()
'MsgBox ListBox1.List(ListBox1.ListIndex, 1) 'Selected full date string.
'MsgBox ListBox1.Value 'Selected full date string.
Unload Me
End Sub
Private Sub ListBox1_AfterUpdate()
On Error Resume Next
TextBox1.Value = Format(CDate(ListBox1.Value), "mmmyyyy")
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer, a(1, 11) As String, s As String, d As Date
With ListBox1
.BoundColumn = 2
.ColumnCount = 2
For i = 0 To 11
d = DateSerial(2011, i + 1, 1)
a(0, i) = Format(d, "mmmm")
a(1, i) = Format(d, "mm/dd/yyyy")
Next i
'http://support.microsoft.com/kb/185388
.Column = a
.ListIndex = 0
End With
End Sub[/VBA]