Hi all,
I'm adding a ListBox on a sheet that displays all the tabs in the workbook. This list is being loaded from an dictionary key array (for unique values) that is generated from an array. This ListBox is initially loaded when the Workbook is first opened. Everything related to this is working appropriately.
My issue is I would like the ListBox to empty prior to populating each time the Workbook is opened. Currently, each time the box is populated on Workbook open, it simply appends to the bottom, rather than refreshing the entire box.
I've read just about everywhere that it should be as simple as a ListBox.Clear, but whenever it hits that line I get an "Object doesn't support this property or method." Note I'm using a Form ListBox - I can't use an ActiveX ListBox due to some vendors having all ActiveX objects blocked.
Anyone got a tip for using a Form listbox on a worksheet (not a userform, if it was there I could simply unload the object) and how to clear it or otherwise make it reset it's values?
Private Sub Workbook_Open()
'Call global variables
Call FileControl.SetGlobalDims
'Call array function
Call FileControl.LoadArrs(True, False)
'Instantiate local objects
Dim tabnames As Object
Dim tabbox As Object
'Instantiate local variables
Dim tabname As Variant
'Define local objects
Set tabnames = CreateObject("Scripting.Dictionary")
Set tabbox = changelog.ListBoxes("TabNameBox")
'Iterate through array to load dictionary
For x1 = LBound(tablist, 2) To UBound(tablist, 2)
If Not tabnames.exists(tablist(4, x1)) Then
tabnames.Add tablist(4, x1), CStr(tablist(4, x1))
End If
Next x1
'Empty listbox (this is the line giving me issues)
tabbox.Clear
'Load list
tabbox.AddItem "All"
For Each tabname In tabnames
tabbox.AddItem tabname
Next tabname
End SubThanks for any help you can provide! Note this is an x-post here: http://www.mrexcel.com/forum/e…clearing.html#post4506485 I will update either/or should it get resolved.