Home VBA temporarily create and delete ListBox after processing array
Reply: 0

VBA temporarily create and delete ListBox after processing array

user1460
1#
user1460 Published in June 20, 2018, 1:24 pm

Don't have much experience with forms/listboxes. The following function aims to remove an array element by passing it to ListBox and using ListBox.RemoveItem method. What i am struggling with is creating/deleting ListBox object programmatically. I do know there are ActiveX and Form ListBox types but recording a code by adding both manually looks identical:

ActiveSheet.ListBoxes.Add(273, 289.5, 72, 71.25).Select

Here is the attempted function:

Function ArraylessElement(arrIn As Variant, ElemNo As Integer) As Variant
    ''''''''''''''''''''''''''''''''
    'removes i element from 1D array by assigning to ListBox
    'returns 1D array w/ Transpose
    ''''''''''''''''''
    Dim lBox As ListBox
    With ActiveSheet
        Set lBox = .ListBoxes.Add(261, 279.75, 72, 71.25)
        lBox.List = arrIn
        lBox.RemoveItem (ElemNo - LBound(arrIn) - 1) '(LBound) of a ComboBox/ ListBox is 0.
        ArraylessElement = Application.Transpose(lBox.List)
        lBox.Delete
    End With
End Function

I'm getting

Unable to set the list property of the ListBox class

error on lBox.List = arrIn line.

PS. I did see a DeleteArrayElement function at http://www.cpearson.com/excel/vbaarrays.htm which i will use if i cannot get mine working.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.460769 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO