Populate Cells from ListBox

[Nigel B]

Hi

I'm having difficulty in populating cells from a ListBox.

Private Sub CommandButton1_Click()
Dim dashboard As Worksheet, ws As Worksheet
Dim RowNum As Long, wbk As Workbook, RowNum1 As Long, ColNum As Long, counter As Integer

Set wbk = ThisWorkbook
Set dashboard = wbk.Sheets("Dashboard")
Set ws = wbk.Sheets("25-07-22")
RowNum = 7
RowNum1 = 31
ColNum = 4
counter = 0

With dashboard

ActionList.Clear
ActionList.Height = 100
ActionList.Width = 1000
ActionList.ColumnCount = 4
ActionList.ColumnWidths = "200;200;200;200"

Do Until ws.Cells(RowNum, 3).Value = ""
If InStr(1, ws.Cells(RowNum, 21).Value, "Yes", vbTextCompare) > 0 Then
On Error GoTo next1

ActionList.AddItem ws.Cells(RowNum, 3).Text
dashboard.Cells(RowNum1, ColNum) = ActionList.List(counter, 0)
ActionList.List(ActionList.ListCount - 1, 1) = RowNum
ActionList.List(ActionList.ListCount - 1, 2) = RowNum1
ActionList.List(ActionList.ListCount - 1, 3) = "Test"

End If

next1:

RowNum = RowNum + 1
RowNum1 = RowNum1 + 1
counter = counter + 1

Loop

End With

End Sub

The code line in red seems to be the offender, I get Run-time error '381' Could not get the List property. Invalid property array index.

I know the solution is going to be glaringly obvious, but i'm having a senior and/or blonde moment.

Any help gladly appeciated

 

[Nigel B]

Hi

I'm having difficulty in populating cells from a ListBox.

Private Sub CommandButton1_Click()
Dim dashboard As Worksheet, ws As Worksheet
Dim RowNum As Long, wbk As Workbook, RowNum1 As Long, ColNum As Long, counter As Integer

Set wbk = ThisWorkbook
Set dashboard = wbk.Sheets("Dashboard")
Set ws = wbk.Sheets("25-07-22")
RowNum = 7
RowNum1 = 31
ColNum = 4
counter = 0

With dashboard

ActionList.Clear
ActionList.Height = 100
ActionList.Width = 1000
ActionList.ColumnCount = 4
ActionList.ColumnWidths = "200;200;200;200"

Do Until ws.Cells(RowNum, 3).Value = ""
If InStr(1, ws.Cells(RowNum, 21).Value, "Yes", vbTextCompare) > 0 Then
On Error GoTo next1

ActionList.AddItem ws.Cells(RowNum, 3).Text
[COLOR=rgb(184, 49, 47)]dashboard.Cells(RowNum1, ColNum) = ActionList.List(counter, 0)[/COLOR]
ActionList.List(ActionList.ListCount - 1, 1) = RowNum
ActionList.List(ActionList.ListCount - 1, 2) = RowNum1
ActionList.List(ActionList.ListCount - 1, 3) = "Test"

End If

next1:

RowNum = RowNum + 1
RowNum1 = RowNum1 + 1
counter = counter + 1

Loop

End With

End Sub

The code line in red seems to be the offender, I get Run-time error '381' Could not get the List property. Invalid property array index.

I know the solution is going to be glaringly obvious, but i'm having a senior and/or blonde moment.

Any help gladly appeciated

If i change the line:

dashboard.Cells(RowNum1, ColNum) = ActionList.List(counter, 0)
to
dashboard.Cells(RowNum1, ColNum) = ActionList.List(0, 0)

That populates the column of cells, but with with the first listbox value multiple times, and blank cells i the column

 

[RoryA]

You should only increment the counter variable if you add an item. Currently you do it at every iteration of the loop.

I'm also not sure why you use a mix of counter and ActionList.ListCount - 1

 

[Nigel B]

You should only increment the counter variable if you add an item. Currently you do it at every iteration of the loop.

I'm also not sure why you use a mix of counter and ActionList.ListCount - 1

Thank you Rory

I couldn't see the forest for the trees

That made all the difference.