Skip to main content
F
fogartyjosiah1-Visitor
1-Visitor
January 16, 2020
Solved

Dynamically Filled List Boxes Error

  • January 16, 2020
  • 1 reply
  • 1538 views

Hello,

 

I'm working on a sheet which should update the options in a second (and third) list-boxes based on the selection of the first and second. (ie. selection a type of steel elements, the second list populates the options for steel grade depending of the element type selected).

 

The bug is that the later boxes don't update until they're clicked on. I want the lists to update automatically once the previous selections are completed. 

 

I'm fairly new to the control script so I think the issue likely lies in my sequence. 

 

Any help?

Best answer by Eng_Math

There is a simple script that you can use to perform this function.  Put this script in the "script" section of the dynamic listbox.  Put the list you want to use as input in the input section of this listbox.

Dim OldList()
ReDim OldList(0)
Dim OnLoad
OnLoad = 1

 

Rem Initialize List Box
Rem ListBox.ResetContent()

Rem Add Strings here as needed

 

Sub ListBoxEvent_Start()
  sel = ListBox.CurSel
  ListBox.CurSel = sel
End Sub

 

Sub ListBoxEvent_Exec(Inputs,Outputs)
  sel = ListBox.CurSel
  If sel < 0 Then
    sel = 0
  End If
  ListBox.CurSel = sel

 

  SelectionList = Inputs(0).Value
  If ListHasChanged(SelectionList,OldList) Then
    ListBox.ResetContent()
    ReDim OldList(UBound(SelectionList))
    For i = 0 to UBound(SelectionList)
      OldList(i) = SelectionList(i)
      ListBox.AddString(SelectionList(i))
    Next
  End If
  If OnLoad Then
    ListBox.CurSel = 0
    OnLoad = 0
  End If
  Outputs(0).Value = ListBox.GetText(sel)
End Sub

 

Sub ListBox_SelChanged()
  ListBox.Recalculate()
End Sub

 

Sub ListBox_DblClick()
  ListBox.Recalculate()
End Sub

 

Function ListHasChanged(List1, List2)
  ListHasChanged = 0
  If UBound(List1) <> UBound(List2) Then
    ListHasChanged = 1
    Exit Function
  End If
  For i = 0 To UBound(List1)
    If List1(i) <> List2(i) Then
      ListHasChanged = 1
      Exit Function
    End If
  Next
End Function

1 reply

E
Eng_Math1-VisitorAnswer
1-Visitor
January 16, 2020

There is a simple script that you can use to perform this function.  Put this script in the "script" section of the dynamic listbox.  Put the list you want to use as input in the input section of this listbox.

Dim OldList()
ReDim OldList(0)
Dim OnLoad
OnLoad = 1

 

Rem Initialize List Box
Rem ListBox.ResetContent()

Rem Add Strings here as needed

 

Sub ListBoxEvent_Start()
  sel = ListBox.CurSel
  ListBox.CurSel = sel
End Sub

 

Sub ListBoxEvent_Exec(Inputs,Outputs)
  sel = ListBox.CurSel
  If sel < 0 Then
    sel = 0
  End If
  ListBox.CurSel = sel

 

  SelectionList = Inputs(0).Value
  If ListHasChanged(SelectionList,OldList) Then
    ListBox.ResetContent()
    ReDim OldList(UBound(SelectionList))
    For i = 0 to UBound(SelectionList)
      OldList(i) = SelectionList(i)
      ListBox.AddString(SelectionList(i))
    Next
  End If
  If OnLoad Then
    ListBox.CurSel = 0
    OnLoad = 0
  End If
  Outputs(0).Value = ListBox.GetText(sel)
End Sub

 

Sub ListBox_SelChanged()
  ListBox.Recalculate()
End Sub

 

Sub ListBox_DblClick()
  ListBox.Recalculate()
End Sub

 

Function ListHasChanged(List1, List2)
  ListHasChanged = 0
  If UBound(List1) <> UBound(List2) Then
    ListHasChanged = 1
    Exit Function
  End If
  For i = 0 To UBound(List1)
    If List1(i) <> List2(i) Then
      ListHasChanged = 1
      Exit Function
    End If
  Next
End Function

Terms & ConditionsCookie settingsAccessibility statement