Guys,

I grabbed this code from Tom:

Private Sub UserForm_Initialize()
Dim Varr As Variant
ComboBox1.RowSource = ""
Varr = Worksheets("Sheet1").Range("A1:A12").Value
For i = LBound(Varr, 1) To UBound(Varr, 1)
Varr(i, 1) = Format(Varr(i, 1), "mmm-yy")
Next i
ComboBox1.List = Varr
End Sub




It now populates the combobox dropdown list with date in the correct
format and it doesnt show any of the exceldates 353434.2323) anymore.

But as soon as you leave the combobox and click anywhere else the date
goes back in the mm/dd/yyyy hh:mm format. Is there a way to force
Excel to leave the format that I defined in Tom's Code?

Matt

What goes back to the mm/dd/yyyy hh:mm format?

You don't mean the selected item in the combobox, do you?

If you put that value into a cell, maybe you can just format the cell the way
you want--either manually or in code???

Matt wrote:

Guys,

I grabbed this code from Tom:

Private Sub UserForm_Initialize()
Dim Varr As Variant
ComboBox1.RowSource = ""
Varr = Worksheets("Sheet1").Range("A1:A12").Value
For i = LBound(Varr, 1) To UBound(Varr, 1)
Varr(i, 1) = Format(Varr(i, 1), "mmm-yy")
Next i
ComboBox1.List = Varr
End Sub

It now populates the combobox dropdown list with date in the correct
format and it doesnt show any of the exceldates 353434.2323) anymore.

But as soon as you leave the combobox and click anywhere else the date
goes back in the mm/dd/yyyy hh:mm format. Is there a way to force
Excel to leave the format that I defined in Tom's Code?

Matt

--

Dave Peterson

What goes back to the mm/dd/yyyy hh:mm format?

The dates in the drop down menu have the right date but they turn back
into mm/dd/yyyy hh:mm as soon as you hit enter or leave the combobox
....

You don't mean the selected item in the combobox, do you?

Yes, the date format of the combobox changes


If you put that value into a cell, maybe you can just format the cell the way
you want--either manually or in code???

Actually I use the combobox to select a date which then goes into a
different cell ... all those cells have the date format I need but in
the combobox its no cooperating :(

Do you have the a cell tied to that combobox--controlsource in its properties?

If yes, dump it and put the value in the cell in code.

Option Explicit

Private Sub ComboBox1_Change()

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

With Worksheets("Sheet1").Range("b1")
.Value = Me.ComboBox1.Value
.NumberFormat = "mmmm dd, yyyy" 'whatever you want
End With

End Sub

But be careful.

Say your combobox shows Dec-03.

Excel will see that as a nice date--December 03 of the current year.

I would think that you would want to keep that date that gets selected as
unambiguous as possible:

Private Sub UserForm_Initialize()
Dim Varr As Variant
Dim i As Long
ComboBox1.RowSource = ""
Varr = Worksheets("Sheet1").Range("A1:A12").Value
For i = LBound(Varr, 1) To UBound(Varr, 1)
Varr(i, 1) = Format(Varr(i, 1), "mmmm dd, yyyy")
Next i
ComboBox1.List = Varr
End Sub



Matt wrote:

What goes back to the mm/dd/yyyy hh:mm format?

The dates in the drop down menu have the right date but they turn back
into mm/dd/yyyy hh:mm as soon as you hit enter or leave the combobox
...

You don't mean the selected item in the combobox, do you?

Yes, the date format of the combobox changes

If you put that value into a cell, maybe you can just format the cell the way
you want--either manually or in code???

Actually I use the combobox to select a date which then goes into a
different cell ... all those cells have the date format I need but in
the combobox its no cooperating :(

--

Dave Peterson

I adapted it to my my code:

Private Sub ComboBox1_Change()


If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If


With Worksheets("Sheet1").Range("AL1")
.Value = Me.ComboBox1.Value
.NumberFormat = "hh:mm:ss - dd mmm yyyy"
End With


End Sub

It still doesnt work :(

The date in the list is like this:

08:36:24 - 01 Oct 2005


As soon as you leave the combobox it turns into that:

10/1/2005 8:36:24 AM

This is killing me! Also now my textbox skips the leading zeros all of
a sudden. Each time I think this project is done, something else
happens....

Matt

Did you remove the linkedcell/controlsource?

Matt wrote:

I adapted it to my my code:

Private Sub ComboBox1_Change()

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

With Worksheets("Sheet1").Range("AL1")
.Value = Me.ComboBox1.Value
.NumberFormat = "hh:mm:ss - dd mmm yyyy"
End With

End Sub

It still doesnt work :(

The date in the list is like this:

08:36:24 - 01 Oct 2005

As soon as you leave the combobox it turns into that:

10/1/2005 8:36:24 AM

This is killing me! Also now my textbox skips the leading zeros all of
a sudden. Each time I think this project is done, something else
happens....

Matt

--

Dave Peterson