ListBox Multiple Selection Issue

[RJC40]

Hi,

I am building a fabrication tracking spreadsheet and have a userform that allows staff members to update the status of an item to "Complete" when the item is finished. The userform contains a listbox in which they can can select multiple items if required, with a command button to confirm the selections.

When running the code "Complete" is added to the top selection only, rather than all selected items?

This is the code I am using - excluding the 'Else' from the IF statement only allows the status to Item 1 to be updated.

Any thoughts would be appreciated!

In addition, I would like to the loop to stop at the last row with data within the listbox (I can have up to 150 items).

Sub SaveSelected()
    
    
Dim i As Long
Dim j As Integer
    
j = 9
          
    With Fabrication.FabStatus
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                Worksheets("Quote_Breakdown").Cells(j, 1).Value = "Complete"
            Else
                j = j + 1
            End If
        Next i
    End With
    
End Sub

 

[davesexcel]

it looks like you may have used the rowsource in the ListBox properties to populate the list box, as soon as the code finds the first selected item, it adds "Complete" to the worksheet, then the listbox repopulates resulting in no more items being selected.

It looks like you can turn off Autocalculate at the beginning of the code and then turning on AutoCalulate at the end of the code. Then the listbox will not reset until after the code has run.
I assume you have j=9 because of the start row.
I don't know if there is a way to stop the code after the last selection. The code does not know if it is selected until it has hits it.

Private Sub CommandButton1_Click()
    Dim i  As Long, j As Integer
   
    Application.Calculation = xlManual
    j = 9
   
    For i = 0 To Me.ListBox1.ListCount - 1
       
        If Me.ListBox1.Selected(i) Then
           
            Worksheets("Quote_Breakdown").Cells(i + 9, 1).Value = "Complete"
           
        End If
    Next i
    Application.Calculation = xlAutomatic
    Unload Me
End Sub

You can also look into adding "Complete" to the selected item as you Double click the selection.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
i = ListBox1.ListIndex
x = IIf(Cells(i + 9, 1).Value = "Complete", "", "Complete")
Cells(i + 9, 1).Value = x
End Sub

 

[RJC40]

Hi Dave,

Many thanks.

I can see what you are saying, that now works perfectly.
In reference to the 2nd part of my question, I think the correct way to resolve that is to only populate the row that have data into the listbox rather than all 150 rows.