Userform Listbox

[Emperor]

Hi all,

I fill a multicolumn Listbox on a Userform with data from a sheet with code;

With ListBox1
    .RowSource = "A4:G128"
    .ColumnHeads = True
    .ColumnCount = 7 'Determine number of columns
    .ColumnWidths = "80;180;180;80;40;60;70" 'Set column widths
    '.List = a 'Insert the range of data supplied
End With

Works perfectly.

Now I wish to loop through the listbox and when items are empty (in 1 of the columns) I need to execute some code, let's say a msgbox.

How can I loop trough the Listbox looking for empty fields in columns 2 to 7?

Mathijs.

 

[Sektor]

Second arguments of List (1, 2, 3) are columns.

Private Sub CommandButton1_Click()
    Dim i As Long
    
    For i = 0 To ListBox1.ListCount - 1
        MsgBox ListBox1.List(i, 1) & " " & ListBox1.List(i, 2) & " " & ListBox1.List(i, 3)
    Next
End Sub

 

[Norie]

Something like this.

Dim rws As Long
Dim cols As Long
 
     For rws = 0 To ListBox1.ListCount - 1
 
            For cols = 1 To ListBox1.ColumnCount - 1 ' not column 1
 
                  If IsEmpty(ListBox1.List(rws, cols)) Then
                        MsgBox 1
                  End If
            Next cols
    Next rws

I used IsEmpty because it didn't seem to work with ="", think that might have been for some other reason.

Anyway, IsEmpty could be a better test.

Just checked ="" does work, think I had the range wrong on first testing.

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG27May19
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] ListBox1
    [COLOR="Navy"]For[/COLOR] Rw = 0 To .ListCount - 1
        [COLOR="Navy"]For[/COLOR] Ac = 0 To .ColumnCount - 1
            [COLOR="Navy"]If[/COLOR] .List(Rw, Ac) = "" [COLOR="Navy"]Then[/COLOR] MsgBox "Row " & Rw & chr(10) & "Col " & Ac
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick