Can't save dependent Listbox

[SteveA]

When I close the worksheet I can not save a listbox selection that is dependent on a previous listbox selection. The first listbox I was able to save the selection using the Metadata , but I could not figure out how to get it to work with the second list box.

[RichardJ]

I don't see any code in the second listbox that attempts to save the state. I'll assume you tried putting some in? Did you use the same name for the metadata item? Because that will not work.

I see you based your script on the script written at PTC. Unfortunately, that script is very badly written. The attached uses a much improved script.

The start and stop routines are obsolete, and have been for at least a decade. They no longer serve any purpose. You should ignore them.

[SteveA]

Yea I didn't include the code in that second listbox because I couldn't get it to work.

I tried to implement the code you provided and its giving me some problems. The second listbox will not update now when I change the selection in the first listbox. Also the sheet is acting really slow when I change a selection in the first listbox. I have attached the updated worksheet.

[RichardJ · Accepted solution]

I tried to implement the code you provided and its giving me some problems. The second listbox will not update now when I change the selection in the first listbox.

The code in the body of the script (i.e. outside of any of the subrountines) only gets executed once, when the worksheet is loaded. That is why setting the OnLoad flag works! The only exception to that rule is that if you edit the script, when you exit the editor the code in the body of the script is executed.

Also the sheet is acting really slow when I change a selection in the first listbox.

I fixed that. Also in the original worksheet I posted, so I have attached another version.

[SteveA]

Thanks for the help that worksheet does exactly what I wanted it to do now.

[NM1]

Great improved script.

I had to add this code above "'Update the metadata" in order to be able to edit input on the fly.

' Force sel to be a valid value, rather than -1. Added to avoid crash when changing input

If CurSel < 0 Then
CurSel = 0
End If

Another question is if it would be possible to change the script to accept matrix input of only one row. If you do coding that generates a dynamic matrix input and the result only have one row, then the script crash with a VBScript error Type mismatch: 'UBound'.

Line:73 in the editied scrip is the line "ListHasChanged = 1":

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

[RichardJ]

I had to add this code above "'Update the metadata" in order to be able to edit input on the fly.

' Force sel to be a valid value, rather than -1. Added to avoid crash when changing input

If CurSel < 0 Then
CurSel = 0
End If

I don't know what you mean by "edit input on the fly". If the list changes the selection is changed to -1 because that means nothing is selected.

Another question is if it would be possible to change the script to accept matrix input of only one row. If you do coding that generates a dynamic matrix input and the result only have one row, then the script crash with a VBScript error Type mismatch: 'UBound'.

Line:73 in the editied scrip is the line "ListHasChanged = 1":

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

Well that function certainly exists in the latest version of the listbox, but not in any version attached to the posts in this thread! You can find an updated listbox that will handle a single string on the input here: Saving the state of controls.zip

[NM1]

Thanks for your resonse.

I downloaded the zip a while ago and found this thread by searching for the file name. I must have downloaded a different version with the same file name at some point.

The version from the link you post http://communities.ptc.com/docs/DOC-1379 has adressed both of the things i posted above so thanks for pointing me in the right direction.