Ken ...

For demo create a userform with 2 blank listboxes..

Private Sub ListBox1_Click()
Me.ListBox2.ListIndex = Me.ListBox1.ListIndex
End Sub


Private Sub UserForm_Initialize()

Dim i%

'set the control source before populating..
'use external address so it will work when form is modeless
Me.ListBox1.ControlSource = [b4].Address(external:=True)
Me.ListBox2.ControlSource = [b5].Address(external:=True)
'format the control sources to different formats
[b4].NumberFormat = "[h]:mm:ss"
[b5].NumberFormat = "hh:mm AM/PM"


'Populate the list with strings..
For i = 0 To 47
Me.ListBox1.AddItem Format(TimeSerial(0, i * 30, 0), "hh:nn am/pm")
Next
Me.ListBox2.List = Me.ListBox1.List

'Note: Minute code mm for excel.numberformat
' nn in vba.format

End Sub


HTH...



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


KenSchnorr wrote:

In this instance, I have populated a list box user form w/ a range of
times in the h:mm AM/PM format and all works well.

However, when I link the listbox form to a cell and format the linked
cell in the same format (which is "1:30 PM" selection in the
formatting dialog box and you can see up above the "Sample = 12:00 AM"
in the formatting dialog box); no matter what I select in my list box,
be it 7:30 PM or 10:00 AM, the linked cell never changes from 12:00
AM, even after selecting many different times in the list box.

If I use a different type time format, say the 37:30:55 type selection
which shows 1248:00:00 as the sample, I do get movement in the linked
cell when I make different selections in my list box, but I want the
other format.

Is there a fix?


---
Message posted from http://www.ExcelForum.com/