excelvbalistbox

How to fix listbox List vs RowSource limitation

Using listbox.List overcame the problems created by using listbox.RowSource as described at Unstable listbox with vba hiding/unhiding Excel worksheet rows

However, RowSource at least provided access to headers as well as automatically updating listbox’s data following adjustments to the reference worksheet. I managed to overcome the loss of headers from the listbox by creating labels above the listbox. But RowSource also synchronized data updates with the listbox. The list property doesn’t. Therefore when updating the worksheet from the userform, I now need to update the altered data in the listbox. I’m struggling to find the means of doing that.

I have tried without success, using vba's WatchWindow to provide some clues as my listbox's properties needed in order to access and alter each listbox.column's value.

Thanks

'''' extracted from userform initialization ''''
shtRow = X        ‘ Worksheet row determined by numerous means
LdRow = X - 4    ‘listDiners equivalent row number

'''' extracted from listbox's initialization sub
With listDiners
    .ColumnCount = 7
    .ColumnWidths = “46, 65, 74, 60, 118, 168, 168”
    .List = shtMeals.Range(“tblDiners”).Value
End With

Sub UpdateRecord
    For c = 2 to 7
        worksheet.Cells(shtRow, c) = userform input value  '' update worksheet record ''
        listDiners.Selected(LdRow, c) = userform input value '' update listbox equivalent column
    Next c
Solution

  • Your input range is essentially a 2D array, like your listbox's List property - you'd update both in a similar way.

    Eg for the list:

    listDiners.List(rowNumber, colNumber) = "hello"

    Note row/column indexes are zero-based for List vs 1-based for the source range.