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

Now I know what yo mean ! Will try that tomorrow morning :)

it works nicely :)

Only problem some of my logic that detects for invalid dates seems to
kick in .. I had a Inumber in there ti prevent text input .. I may have
to take that out. But still have to prevent the user from entering a
not date ...


Something else I am thinking is that my data comes like that:

8/12/2004 21:41:46
8/12/2004 21:43:46
8/12/2004 21:45:46
8/12/2004 21:47:46
8/12/2004 21:49:46
8/12/2004 21:51:46
8/12/2004 21:53:46
8/12/2004 21:55:46
8/12/2004 21:57:46
8/12/2004 21:59:46
8/12/2004 22:01:46

The user essentially has to enter a date which I simplified by having
them select it from the combobox which shows all possible logged dates.

But they will never enteer anything else than 00 seconds. And they may
have to enter a minute that is in between the loggedones (it logs every
two minutes).

- Is there a way to populate the combox with additional dates?
- Also is there a way to alwyas have the seconds be 00? No mater what
the source data is like?

Existing Data:
8/12/2004 21:41:46
8/12/2004 21:43:46

I need it like this:

8/12/2004 21:41:00 (seconds changed to 00)
8/12/2004 21:42:00 (Entire date added)
8/12/2004 21:43:00 (seconds changed to 00)

:) Matt

Dave Peterson wrote:
Did you remove the linkedcell/controlsource?

It works, it leaves the date format in the combobox alone, but with the
controlsource removed, how do i get the value in the combobox in the
cell where I need it(AL1) ?

matt

I retract my question. I got it.

For people with my skill level ;)

Private Sub Combobox1_Change()
Range("AL1").Value = ComboBox1.Value
End Sub

Matt

I retract my question. I got it.

For people with my skill level ;)

Private Sub Combobox1_Change()
Range("AL1").Value = ComboBox1.Value
End Sub

Matt

if you can find the first date and the last date, then you could use something
like:

Option Explicit
Sub testme01()

Dim StartDate As Date
Dim AdjStartDate As Date
Dim LastDate As Date
Dim AdjLastDate As Date
Dim myDate As Date
Dim iCtr As Long

'I just started with what you gave me.
StartDate = DateSerial(2005, 8, 12) + TimeSerial(21, 41, 46)
LastDate = DateSerial(2005, 8, 12) + TimeSerial(21, 43, 46)

AdjStartDate = Int(StartDate) _
+ TimeSerial(Hour(StartDate), Minute(StartDate), 0)
AdjLastDate = Int(LastDate) _
+ TimeSerial(Hour(LastDate), Minute(LastDate), 0)

Worksheets("sheet1").ComboBox3.Clear

iCtr = -1
Do
iCtr = iCtr + 1 'start with 0
myDate = AdjStartDate + TimeSerial(0, iCtr, 0)
If myDate AdjLastDate Then
Exit Do
End If

Worksheets("sheet1").ComboBox3.AddItem _
Format(myDate, "mmmm dd, yyyy hh:mm:ss")
Loop

End Sub

I would think that you could use something like:

Startdate= application.min(worksheets("sheet99").range("a:a") )
Lastdate = application.max(worksheets("sheet99").range("a:a") )


maybe?????



Matt wrote:

it works nicely :)

Only problem some of my logic that detects for invalid dates seems to
kick in .. I had a Inumber in there ti prevent text input .. I may have
to take that out. But still have to prevent the user from entering a
not date ...

Something else I am thinking is that my data comes like that:

8/12/2004 21:41:46
8/12/2004 21:43:46
8/12/2004 21:45:46
8/12/2004 21:47:46
8/12/2004 21:49:46
8/12/2004 21:51:46
8/12/2004 21:53:46
8/12/2004 21:55:46
8/12/2004 21:57:46
8/12/2004 21:59:46
8/12/2004 22:01:46

The user essentially has to enter a date which I simplified by having
them select it from the combobox which shows all possible logged dates.

But they will never enteer anything else than 00 seconds. And they may
have to enter a minute that is in between the loggedones (it logs every
two minutes).

- Is there a way to populate the combox with additional dates?
- Also is there a way to alwyas have the seconds be 00? No mater what
the source data is like?

Existing Data:
8/12/2004 21:41:46
8/12/2004 21:43:46

I need it like this:

8/12/2004 21:41:00 (seconds changed to 00)
8/12/2004 21:42:00 (Entire date added)
8/12/2004 21:43:00 (seconds changed to 00)

:) Matt

--

Dave Peterson

I tried it ... it has 2 minute intervals. Had to modify the code to
get it to run but shouldnt have any impact.

I can detect the first and last cell, thats no problem
:) Matt