0

I'm developing a program within Excel and VBA, and am getting a run time error 380 when I try to change the selected property of a listbox.

The property is determined by a column on an excel spreadsheet, containing True or False values. I've printed the contents of these cells to the console and can confirm that the true/false values are working correctly, however when I try to assign these values to my listbox.selected property I get an error.

The function is below, any help or suggestions would be much appreciated.Error occurs on this line:

ElementListBox.Selected(count - 1) = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value

Public Function LoadTier2SubTaskList(ByVal Task As Single, ByRef ElementListBox As Control)

ElementListBox.Clear
WorklistComboBox.Clear


Dim count As Single
Dim finished As Boolean
Dim TaskListSheet As Worksheet
Set TaskListSheet = TBSheet
finished = False

For count = 1 To 50

Next count

count = 1
Dim TaskString As String

Do While finished = False
TaskString = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.ElementOfWork)).Text
If TaskString = vbNullString Then
finished = True

ElseIf TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Tier)).Value = 2 Then

ElementListBox.AddItem (TaskString)
ElementListBox.Selected(count - 1) = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value
Debug.Print (TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value)
End If

count = count + 1
Loop

End Function
Steffan Davies
  • 149
  • 1
  • 2
  • 10
  • What is that `for count=1 to 50` loop doing? Just counting? Then count is back to 1? so your `listbox.selected(count-1)` is `listbox.selected(0)`? – Raystafarian Jul 21 '15 at 11:46
  • What are `TaskListCellRef`, `ref`, `TaskBreakdownColunRefs`, and `TaskBreakdownColumnHeaders`? They don't appear to be defined to set anywhere in the procedure. – Dick Kusleika Jul 21 '15 at 12:48
  • Good spot with that for loop, not sure why that was there but removing it didn't make any difference. TaskListCellRef etc are cell references that are set up elsewhere within the code. They are referencing the correct cells, I tested that using the debugger, – Steffan Davies Jul 21 '15 at 13:37

1 Answers1

0

Do you need to convert that value to a boolean?

ElementListBox.Selected(count - 1) = CBool(TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value)

Let's start eliminating some potential causes!

Does this work?

ElementListBox.Selected(count - 1) = true

Try this (replace your code from count=1 to Loop)...

count = 1
Dim itemCount as Integer
itemCount = 1
Dim TaskString As String

Do While finished = False
TaskString = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.ElementOfWork)).Text
If TaskString = vbNullString Then
finished = True

ElseIf TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Tier)).Value = 2 Then

ElementListBox.AddItem (TaskString)
ElementListBox.Selected(itemCount - 1) =    CBool(TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1,  TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value)
Debug.Print (TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value)
itemCount = itemCount +1
End If

count = count + 1
Loop

Note the addition of the itemCount variable to count the number of items in the listbox

3-14159265358979323846264
  • 3,250
  • 2
  • 15
  • 38
  • No luck unfortunately – Steffan Davies Jul 21 '15 at 13:37
  • Which line do you get the error on? Also, looking here seems to imply there are quite a few random causes for this error ... http://stackoverflow.com/questions/1979281/what-causes-this-error-runtime-error-380-invalid-property-value – 3-14159265358979323846264 Jul 21 '15 at 13:39
  • That line, ElementListBox.Selected(count - 1) = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.Included)).Value – Steffan Davies Jul 21 '15 at 13:47
  • Nope, i think the problem lies with count/the loop. However the count works exactly how I want it to on other lines in this function. (eg:TaskString = TaskListSheet.Cells(TaskListCellRef(Task, ref.Row) + count + 1, TaskBreakdownColumnRefs(TaskBreakdownColumnHeaders.ElementOfWork)).Text) – Steffan Davies Jul 21 '15 at 14:01
  • If that doesn't work, then try setting `itemCount=0` instead of `itemCount=1` – 3-14159265358979323846264 Jul 21 '15 at 14:17