Formatting the linked cell when using a List Box User Form
 

In this instance, I have populated a list box user form w/ a range o
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 linke
cell in the same format (which is "1:30 PM" selection in the formattin
dialog box and you can see up above the "Sample = 12:00 AM" in th
formatting dialog box); no matter what I select in my list box, be i
7:30 PM or 10:00 AM, the linked cell never changes from 12:00 AM, eve
after selecting many different times in the list box.

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

Is there a fix

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

Formatting the linked cell when using a List Box User Form
 

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/

Formatting the linked cell when using a List Box User Form
 

Thank you for the example, but it is a little more than I am up t
because all of this is new, to me. I am trying to use one embedde
list box, if that helps.

In your example, I was able to create the userform and make it work
but when I try to modify the code for a listbox that is not on a use
form, I get errors. Please help

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