Multiple columns (more than 10) in a listbox with varying formats

Hi everyone,

I’m working on a case management system that is user form based that will be run in Excel 2003 on limited UK Government IT hardware. The forms pull information from the relevant sheets (each case has a separate sheet) and some general information is collated from a central sheet. One of my senior colleagues has requested that on the home screen we display an overview of the all cases current status and related targeted dates, this totals 14 case specific bits of information. Getting the information into the central sheet (Sheet Admin) isn’t the problem, however getting the information displayed on the Home user form is.

The way I've got set it up (so far) is that on a user form there is a big list box and several case type buttons. What I want to achieve is that when a button is clicked the list of corresponding cases is displayed within the list box. So for example when 'all cases' is clicked all cases within the list are displayed or when 'Court Applications' is clicked all cases is clicked all the cases awaiting a Court Order are shown.

However with the list box I’m finding trouble on trying to go pass 10 columns and I’m having trouble presenting the dates. I got around the dates use with the following code displaying them in (DD MMM YY format) however I’m unsure as to how to get this beyond the 10 columns to 14.

p = the case type selected

If ActiveCell.Offset(0, 1).Value = p  Then

    With CUCMS_Home.case_list
        CUCMS_Home.case_list.ColumnCount = 14
        .AddItem
        .List(LstIndex, 0) = ActiveCell.Value
        .List(LstIndex, 1) = ActiveCell.Offset(0, 1).Value
        .List(LstIndex, 2) = ActiveCell.Offset(0, 2).Value
        .List(LstIndex, 3) = ActiveCell.Offset(0, 3).Value
        .List(LstIndex, 4) = Format(ActiveCell.Offset(0, 4).Value, "dd mmm yy")
        .List(LstIndex, 5) = Format(ActiveCell.Offset(0, 5).Value, "dd mmm yy")
        .List(LstIndex, 6) = Format(ActiveCell.Offset(0, 6).Value, "dd mmm yy")
        .List(LstIndex, 7) = Format(ActiveCell.Offset(0, 7).Value, "dd mmm yy")
        .List(LstIndex, 8) = Format(ActiveCell.Offset(0, 8).Value, "dd mmm yy")
        .List(LstIndex, 9) = Format(ActiveCell.Offset(0, 9).Value, "dd mmm yy")
        .List(LstIndex, 10) = Format(ActiveCell.Offset(0, 10).Value, "dd mmm yy")
        .List(LstIndex, 11) = Format(ActiveCell.Offset(0, 11).Value, "dd mmm yy")
        .List(LstIndex, 12) = Format(ActiveCell.Offset(0, 12).Value, "dd mmm yy")
        .List(LstIndex, 13) = Format(ActiveCell.Offset(0, 13).Value, "dd mmm yy")
        .List(LstIndex, 14) = Format(ActiveCell.Offset(0, 14).Value, "dd mmm yy")
    End With
LstIndex = LstIndex + 1
End If

ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""

I also want to condense the code down as much as possible, at present it takes over 90 seconds to load.

Thank you in advance for your suggestions! I really need to get this sorted ASAP, I put my neck on the line to deliver this system and it’s sending me crazy!

thanks Jbeaucaire from making the post complie with the fourm rules, sorry i forgot

Try creating an array with the data you want in the listbox and then using List to populate.

Hi,

I tried that but i havent got much experience in this area. The code I'm working with is:

LstIndex = 0
q = 0
p = "test"



ActiveSheet.Cells(5, 15).Select
LstIndex = 0
Do
With CUCMS_Home.case_list
    CUCMS_Home.case_list.ColumnCount = 14
    If ActiveCell.Offset(0, 1).Value = p Or ActiveCell.Row = 5 Then
            .AddItem
        .List(LstIndex, 0) = ActiveCell.Value
        .List(LstIndex, 1) = ActiveCell.Offset(0, 1).Value
        .List(LstIndex, 2) = ActiveCell.Offset(0, 2).Value
        .List(LstIndex, 3) = ActiveCell.Offset(0, 3).Value
        .List(LstIndex, 4) = ActiveCell.Offset(0, 4).Value
        .List(LstIndex, 5) = Format(ActiveCell.Offset(0, 5).Value, "dd mmm yy")
        .List(LstIndex, 6) = Format(ActiveCell.Offset(0, 6).Value, "dd mmm yy")
        .List(LstIndex, 7) = Format(ActiveCell.Offset(0, 7).Value, "dd mmm yy")
        .List(LstIndex, 8) = Format(ActiveCell.Offset(0, 8).Value, "dd mmm yy")
        .List(LstIndex, 9) = Format(ActiveCell.Offset(0, 9).Value, "dd mmm yy")
        .List(LstIndex, 10) = Format(ActiveCell.Offset(0, 10).Value, "dd mmm yy")
        .List(LstIndex, 11) = Format(ActiveCell.Offset(0, 11).Value, "dd mmm yy")
        .List(LstIndex, 12) = Format(ActiveCell.Offset(0, 12).Value, "dd mmm yy")
        .List(LstIndex, 13) = Format(ActiveCell.Offset(0, 13).Value, "dd mmm yy")
        LstIndex = LstIndex + 1
    End If
End With

ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""

But unfortunetly I'm still having the columns problem. Any suggestions?

Iain

What did you try for the array?

Any chance you could attach a sample workbook?

you can't use additem-you have to create the entire array first as Norie said and then assign that to the List property

Unfortunetly as im at work (on a restricted pc) which wont let me upload the file to the website, I'll upload a copy tonight for you.