List Box Item Format

[dunnerca]

I'm working in Excel 2003. I've created a form containing a 4 column list
box. The data for the list box comes from an inventory sheet. Columns 2 and
3 of the inventory sheet are times (starting and ending). On the sheet I've
formatted these columns in a custom format to show, for example, 4:00 PM and
6:00 PM as the start and end times. However, when I populate the list box
using the following command, the times show as, for example, ".66667", and
not the format I've used in the worksheet.

Me.lst_showdata.List = Sheets("inventory").Range("inv_data").Value

Is there an easy way to format the columns when I'm populating the list box?

Thanks.

 

[Joel]

Use Text instead of value
Me.lst_showdata.List = Sheets("inventory").Range("inv_data").Text

 

[Dave Peterson]

I think you'll find that you'll have to loop through the cells and use .additem.

Then you can use

Dim myCell As Range
For Each myCell In Sheets("inventory").Range("inv_data").Cells
Me.lst_showdata.AddItem myCell.Text
Next myCell

or format the entry yourself

Dim myCell As Range
For Each myCell In Sheets("inventory").Range("inv_data").Cells
Me.lst_showdata.AddItem Format(myCell.Value, "hh:mm AM/PM")
Next myCell

 

[dunnerca]

Thanks for the replies, Joel and Dave. I tried the "text" but I think Dave
was correct that I have to do a loop.

I'm assuming, since this is a 4-column listbox, that I have to do a sub loop
to get the data in the correct columns.

Thanks for your assistance.

 

[Dave Peterson]

You could use another loop to offset the values to add to the listbox--or you
could just be explicit:

Dim myCell As Range
With Me.lst_showdata
.Clear
.ColumnCount = 4
For Each myCell In Sheets("inventory").Range("inv_data").Cells
.AddItem myCell.Text
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Text
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Text
.List(.ListCount - 1, 3) = myCell.Offset(0, 2).Text
Next myCell
End With

Depending on the order (notice my .offset()'s), it may be easier to not loop.