Userform Listbox Refresh

[Simon2001]

HI

Not a programmer but hopefully doing a good job of finding and playing around with code to get what I want.

I've created a spreadsheet that has raw data on a worksheet. I have one macro that scans through the data and the last column (J:J) is changed to REVIEW DUE, FOLLOW-UP or COMPLETE if the row column A entry appears on a separate reviews worksheet. This is based on a workflow that sees the user complete a task, copies its value on to the reviews sheet and then its marked as complete on the original sheet.

To complete the review, I have created a userform which on it has a listbox that populates it contents for ONLY those rows on the Raw Data worksheet that are at REVIEW DUE or FOLLOW-UP. Basically anything that is due for a review or follow-up.

It works perfect first time round but when the userform is closed by using the OK button to copy the entry back to the sheet and re-process to COMPLETE, the next time I open the userform, the listbox still contains the previous value when I want it to disappear. So the user simply works through all reviews until the list box is empty.

I've tried various methods to empty or clear the listbox when the userform is initialized but cant seem, to get anything to work.

Hopefully Im doing this right, but this is the code to open the userform and populate the listbox based on the data on the raw data sheet.

Sub UserForm_Initialize()    Dim dataRange As Range
    Dim oneCell As Range
    Dim i As Long
    
    ListBox1.Clear
   
    With Worksheets("Raw Data")
        Set dataRange = Range(.Cells(Rows.Count, 1).End(xlUp), .Range("j8"))
    End With
    
    ListBox1.ColumnCount = dataRange.Columns.Count - 0
    ListBox1.List = dataRange.Resize(1, dataRange.Columns.Count - 0).Value
    
    For Each oneCell In dataRange.Columns(10).Cells
        If oneCell.Value = "REVIEW DUE" Or oneCell.Value = "FOLLOW-UP" Then
            With oneCell.EntireRow
                ListBox1.AddItem .Cells(1, 1).Value
                For i = 1 To ListBox1.ColumnCount - 1
                    ListBox1.List(ListBox1.ListCount - 1, i) = .Cells(1, i + 1).Value
                Next i
            End With
        End If
    Next oneCell
    ListBox1.RemoveItem 0


End Sub

Any ideas would be appreciated!

 

[gallen]

Hello and welcome to the forums

Only issue I can think of is that when you 'close' the userform, it isn't being destroyed, just hidden, and therefore the initialize sub won't call again.

Make sure when you close the form it is set to nothing OR put the above code in a separate sub called 'FillList' or something and then call that routine from both the Initialize AND the Activate event.

If that doesn't make sense let me know

 

[Simon2001]

Ah, well of course that makes total sense!! As soon as I read your reply, of course I am only hiding the form so its bound to be the same and will not re-start!

Brilliant, using unload now and works perfectly.

Thanks very much

 

[Yongle]

Another option for you to try:

insert as LAST line of "OK" button code

 Unload Me

EDIT - I took too long to respond

 

[Simon2001]

Thanks for help!