Read lines from Excel file and list them in form's ListBox

[pitaszek]

Hi Guys,

My idea is to read the values from the Excel file and list them in ListBox. The idea is a user reads all currently Open tickets that are stored in a workbook and has them displayed in the list box. Then selects an item he'd like to close and just clicks 'Close' button - the status in the Excel file is changed to Closed.

I need a solution, a loop to go through open items and list them. I don't know how to get the Excel item listed appear in the listbox - this is my main problem.

The result should be as below:

____________________________________________
|Task Name ........................................... | Status |
|Pricing ................................................... | Open . |
|Access Management................................. | Open.. |
|Prepare a report for Dave.......................... | Open.. |
|___________________________________________|

_______
| Close |
|______|


Input data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Task name
[/TD]
[TD]Date
[/TD]
[TD]Owner
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]Pricing
[/TD]
[TD]dd/mm/yy
[/TD]
[TD]Agent A
[/TD]
[TD]Open
[/TD]
[/TR]
[TR]
[TD]Reconciliation
[/TD]
[TD]dd/mm/yy
[/TD]
[TD]Agent A
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]Accesses management
[/TD]
[TD]dd/mm/yy
[/TD]
[TD]Agent B
[/TD]
[TD]Open
[/TD]
[/TR]
[TR]
[TD]Send email to Rick
[/TD]
[TD]dd/mm/yy
[/TD]
[TD]Agent B
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]Prepare a report for Dave
[/TD]
[TD]dd/mm/yy
[/TD]
[TD]Agent B
[/TD]
[TD]Open
[/TD]
[/TR]
</tbody>[/TABLE]

Regards,
Witek

 

[My Aswer Is This]

So all these values are in a worksheet correct?
And can we assume "Task name is in column "A" starting in row (2)
And "Status" is in column "D"

Is this correct?

And we will be working with one Activex Listbox and one Activex Command button

 

[pitaszek]

So all these values are in a worksheet correct?
And can we assume "Task name is in column "A" starting in row (2)
And "Status" is in column "D"

Is this correct?

And we will be working with one Activex Listbox and one Activex Command button

Yes. That's right. That would be enough to get how to achieve that. I can further expand and adjust the actual columns captured to my needs.
Yes, Activex.

Witek

 

[My Aswer Is This]

Try this:
Assuming you have a listbox named "ListBox1"

You will also need a command button to load the values into the list box
This script will load values in column "A" and column "D" into the listbox

And you will need another command button to run your script.

Click on the listbox row you want to change and then click your command button with the second script

Now this script will only work with you selecting just one row in the listbox

Your not allowed to choose more then one row in the listbox. You did not say you wanted to select more then one.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'Loads values into listbox
ListBox1.ColumnCount = 2
ListBox1.Clear
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
b = 0
    For i = 2 To Lastrow
            
        With ListBox1
            .AddItem
            .List(b, 0) = Cells(i, 1).Value
            .List(b, 1) = Cells(i, 4).Value
                b = b + 1
        End With
    Next
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton2_Click()
'Mark column "D" as "Closed"
Application.ScreenUpdating = False
If ListBox1.ListIndex < 0 Then MsgBox "You must select some row in the List Box": Exit Sub
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value = ListBox1.List(ListBox1.ListIndex, 0) And Cells(i, 4).Value = "Open" Then Cells(i, 4).Value = "Closed"
    Next
Application.ScreenUpdating = True
End Sub

 

[pitaszek]

Thank you very much for your reply and engagement. I will play around with the codes but I think this should be fine for me.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you very much for your reply and engagement. I will play around with the codes but I think this should be fine for me.