populate VBA list box from row source and update multiple rows

Question

i am trying to populate VBA userform listbox with below code. It works if i select range from A to F column. however if i change A to L, it gives me an error.

can you help me to correct below code?

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("part bump")

    Dim Last_Row As Long
    Dim r, c As Range

    Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

    With UserForm3
       
        .lstDatabase.ColumnCount = 11
        .lstDatabase.ColumnHeads = True
        .lstDatabase.ColumnWidths = "20,40,40,40,2,60,60,60,60,300,60"
        
        Set r = sh.Range("A4:F" & Last_Row)
     
        i = 0
        For Each d In r.Rows
            j = 0
            For Each c In d.Cells
                UserForm3.lstDatabase.AddItem
     
                UserForm3.lstDatabase.List(i, j) = c.Value
                j = j + 1
     
            Next c
            i = i + 1
        Next d
     
        If Last_Row = 1 Then
            UserForm3.lstDatabase.RowSource = "part bump!A4:F4"
        
        End If
        
    End With

Below code use to update multiple selected rows under userform. it only updates 1st selected row not all selected rows.

    Private Sub cmdaction_Click()
    Dim t, t1 As String
  Dim vrech As Range, lColumn As Range
  Dim sh As Worksheet
  Dim i As Long
  Dim selItem As String
  
  Set sh = ThisWorkbook.Sheets("part bump")
  Set lColumn = sh.Range("H1:AZA1").Find(Val(txtchangenumber.Value), , xlValues, xlWhole)
  If lColumn Is Nothing Then
    MsgBox "Column not found"
    Exit Sub
  End If
  
  With UserForm3.lstDatabase
    For i = 0 To UserForm3.lstDatabase.ListCount - 1
    
      If UserForm3.lstDatabase.Selected(i) = True Then
        Set vrech = sh.Range("E3:E250").Find(.Column(4, i), , xlValues, xlWhole)
        If Not vrech Is Nothing Then
          Select Case cmbaction.Value
            Case "RP"
              t = Chr(Asc(Mid(.List(i, 4), 2, 1)) + 1)
              'Me.lstDatabase.Row (0), Column(4) = "ABA"
              t1 = Mid(.List(i, 4), 1, 1) & t & Mid(.List(i, 4), 3, 1)
              Intersect(vrech.EntireRow, lColumn.EntireColumn) = t1
          Case "RV"
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = .List(i, 4)
          Case "DP"
            Intersect(vrech.EntireRow, lColumn.EntireColumn) = "Deleted"
            vrech.EntireRow.Font.Strikethrough = True
          End Select
        End If
      End If
      
    Next i
  End With
End Sub

score 0 · Accepted Answer · answered Aug 19 '20 at 15:46

Populate Listbox

It's not clear for me whether you want to assign range data

a) via the listbox'es .RowSource property (showing headers) or
b) via the listbox'es .List property (allowing no headers)

I demonstrate both approaches modifying your original code.

Furthermore I suggest to move your code into the form's own code module - the UserForm_Initialize handler would be a good place for this. C.f.

Version a)

Note that you have to include a blank delimited sheet name (part bump) within "'", e.g. via

.RowSource = "'part bump'!A4:L17"

or

.RowSource = "'" & sh.Name & "'!" & rng.Address

Private Sub UserForm_Initialize()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("part bump")
    
    Const HeaderRow As Long = 3
    Dim LastRow     As Long
    LastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    If LastRow = HeaderRow Then LastRow = HeaderRow + 1 ' provide for empty data
    
    Dim rng As Range
    Set rng = sh.Range("A" & HeaderRow + 1 & ":L" & LastRow)
    Debug.Print rng.Address
    
    With lstDatabase
        .ColumnCount = 11
        .ColumnWidths = "20;40;40;40;2;60;60;60;60;300;60"
        
        'a) Row Source plus headers
        .ColumnHeads = True
        .RowSource = "'" & sh.Name & "'!" & rng.Address   ' << don't forget "'" around sheet name!

    End With
    
End Sub

Version b)

"It works if i select range from A to F column. however if i change A to L, it give me an error."

Adding data via the .AddItem method has a not documented limitation of 10 columns only
(these are provided automatically as empty array items of the .List property). Therefore it's not possible to reference a (zero-based) column index of 10 or more as it doesn't exist.

You can shorten your code and overcome this limitation by assigning an entire data field array to the listbox'es .List property. c.f.: Populate listbox with multiple columns

Private Sub UserForm_Initialize()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("part bump")
    
    Const HeaderRow As Long = 3
    Dim LastRow     As Long
    LastRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
    If LastRow = HeaderRow Then LastRow = HeaderRow + 1 ' provide for empty data
    
    Dim rng As Range
    Set rng = sh.Range("A" & HeaderRow + 1 & ":L" & LastRow)
    Debug.Print rng.Address
    
    With lstDatabase
        .ColumnCount = 11
        .ColumnWidths = "20;40;40;40;2;60;60;60;60;300;60"
        
        'b) alternatively via array assignment (without headers!)
        '   allows to overcome 10 column limitation of .AddItem
        .ColumnHeads = False

        .List = rng.Value   ' << assign data field array as a whole to .List
    End With
    
End Sub

Further hint

Your declaration in OP (Dim r, c As Range) intends to declare a Range data type to both variables, but it fails as VBA assumes Variant for r if not declare expressly (Dim r as Range, c As Range).

thanks for detail response. This helps me to understand listbox row source. both codes works as intended. with version A, when i try to update multiple rows under multiselect listbox, it only changes 1st selected row. can you pl help me to modify code so i am able to update multiple rows with version one as i need to have header. thanks — jay6939, Aug 20 '20 at 04:35
My solution assumes your header row in row 3, if not you should change the definition `Const HeaderRow As Long = 3` to the actual row number. A working header functionality via `.RowSource` always defines the data range to start one row below header. Did you write `UserForm_Initialize` into the form's code module? Plz re-check the code. - @jay6939 — T.M., Aug 20 '20 at 05:23
Thanks for feedback. with version A. listbox populate with Header from Row 3 and list populate from row 4 of row source. it works as intented. when i try to update multiple rows under listbox, it only updates 1st selected row and not all selected rows. i am using below code to update multiple rows with command button, — jay6939, Aug 20 '20 at 05:41
I have updated initial post with code i am using to update multiple rows under listbox. can you pl help to find where i am making mistake? — jay6939, Aug 20 '20 at 05:48
@jay6939 Glad that *"it works as intended"*. If helpful feel free to accept by ticking the green checkmark. It's good use at SO to pose *another* question if you have *another* issue and not to expand the original post. It would be helpful to add some data to such a new question describing what you want to update by example :-) — T.M., Aug 22 '20 at 18:37

populate VBA list box from row source and update multiple rows

1 Answers1