List Box Item Format
 

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.

List Box Item Format
 

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

"dunnerca" wrote:

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.

List Box Item Format
 

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 wrote:

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.

--

Dave Peterson

List Box Item Format
 

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" wrote:

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 wrote:

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.

--

Dave Peterson

List Box Item Format
 

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.

dunnerca wrote:

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" wrote:

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 wrote:

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.

--

Dave Peterson


--

Dave Peterson