List Box Item Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dunnerca - 13 May 2008 04:13 GMT

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.

Reply to this Message

Joel - 13 May 2008 05:18 GMT

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

> 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
[quoted text clipped - 9 lines]
>
> Thanks.

Reply to this Message

Dave Peterson - 13 May 2008 12:50 GMT

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

> 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
[quoted text clipped - 9 lines]
>
> Thanks.

Signature

Dave Peterson

Reply to this Message

dunnerca - 13 May 2008 14:11 GMT

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.

> I think you'll find that you'll have to loop through the cells and use .additem.
>
[quoted text clipped - 25 lines]
> >
> > Thanks.

Reply to this Message

Dave Peterson - 13 May 2008 15:55 GMT

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.

> Thanks for the replies, Joel and Dave. I tried the "text" but I think Dave
> was correct that I have to do a loop.
[quoted text clipped - 37 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message