Trouble using VBA to create ActiveX ListBox on a Worksheet

[NigelTufnel]

Apologies in advance for what will be a long post, but I've tried a lot of things and I want to be as clear as possible in explaining my problem to maximize the likelihood that someone can help.

To sum up my problem, I can place an ActiveX control on my worksheet, but I cannot seem to edit its properties as I desire. My head is about to explode after hours of trying to understand OLEobjects and ControlFormat properties.

I am a novice VBA programmer with years of VBA programming, yet with little experience dealing with ActiveX controls. I am using Excel 2007 in Windows 7.

I am trying to place a multi-select listbox control (including checkboxes) on an Excel worksheet programmatically. I can easily place this control and format it to my heart's content from the Developer Menu in the Excel 2007 Ribbon interface, but I need to be able to do this from within VBA. (The user of my application will be creating these list boxes by pressing a button.)

I'd prefer to use the simpler Forms ListBox control, but from what I can tell, it doesn't seem to allow the option to place checkboxes next to the list items, which is a feature my users would find very helpful.

OK, so I recorded a macro in which I placed the ListBox ActiveX control on my worksheet and set the properties using the properties dialog box. The first thing I noticed was that the control properties I selected during the session were not recorded in the macro. Bummer. I then modified the code to produce the following snippet:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=369, Top:=174, Width:=106.8, Height:=54.6 _
    ).Select
        
Selection.ListFillRange = "MyRange"

The ListBox was successfully filled with the desired items. So far, so good.

Then I added the following line...and it gave me an error:

 Selection.MultiSelect = fmMultiSelectMulti

I get the dreaded "Object does not support this property or method". I didn't expect this. All I'm doing is setting the property to allow multiple selections.

So, problem #1 is...how do I get access to all of the properties shown for the List Box in the Properties Window?

The next problem came when I tried to give the ListBox a specific name. I replaced the code above with the following:

ListBoxName = "Temp"
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=369, Top:=174, Width:=106.8, Height:=54.6 _
        ).Name = ListBoxName
ActiveSheet.Shapes(ListBoxName).ControlFormat.ListFillRange = "MyRange"

This code, when executed, resulted in a blank list box, and an error: "Object does not support this property or method." Even if I replace the text "ListBoxName" with the index number of the List Box in the Shapes collection, I still get an error.

OK, so I did more research and tried to set the properties using the following syntax:

ActiveSheet.Temp.ListFillRange="MyRange"
Activesheet.Temp.MultiSelect=fmMultiSelectMulti
Activesheet.Temp.ListStyle=fmListStyleOption

This also gave me an "Object does not support this property or method." error from VBA.

But then I copied these three lines verbatim to the VBE Immediate Window and they all executed to perfection when I tried them one at a time. Why they work in the immediate window and not as programmed code, I have no idea.

Of course, even if I get that syntax to work in my code, it doesn't solve my problem as the List Box name "Temp" is a constant in these expressions, whereas I want to be able to refer to the list box using a variable (as I will have multiple list boxes on the same worksheet).

At this point, I am completely frustrated and out of my league. I clearly lack some basic understanding of ActiveX controls and how they are manipulated using VBA syntax.

Can someone help me here? I'd settle for knowing how to code this correctly, but if someone can explain (or give me a reference to) how to understand ActiveX controls on worksheets (as opposed to forms) I'd be most appreciative.

Thanks for your patience.

 

[RoryA]

Here's some sample code that may help - I've added comments where relevant:

Sub AddOLEListbox()
    Dim objOLE As OLEObject, objListBox As MSForms.ListBox
    Set objOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=369, Top:=174, Width:=106.8, Height:=54.6)
    
    ' set some properties
    With objOLE
        ' these are properties of the container OLEObject, not the Listbox itself
        .Name = "blah"
        .ListFillRange = "MyRange"
        ' multiselect is a property of the listbox contained within the OLEObject
        ' so we must us the Object property to get a reference to the listbox
        Set objListBox = .Object
        ' toggle visibility to ensure the control is clickable
        .Visible = False
        .Visible = True
    End With
    ' now we can set the listbox-specific properties
    With objListBox
        .MultiSelect = fmMultiSelectMulti
        .MatchEntry = fmMatchEntryComplete
    End With
    
End Sub

 

[NigelTufnel]

Thanks, Rory. It looks very helpful.

 

[NigelTufnel]

Rory--

I was able to successfully implement your suggestion. I had some difficulty getting the list box sized correctly--it would automatically size the box either too large or too small--but I eventually worked that out by setting the ListFillRange as the last property modified.

I ran into another difficulty and wondered if you or anyone else could help.

The listboxes I create are consistently named in my application to be in the range of ListBox1 to ListBox9

I want to create a data validation list in a particular cell based on the selected contents of each listbox. I've successfully written code that accomplishes this, but I'm having trouble getting it to run automatically.

I created a ListBox1 using my macro and then created a ListBox1_Click() event on the worksheet that contains ListBox1:

Private Sub ListBox1_Click()
  MsgBox ("First List Box Clicked")
End Sub

However, this event does not run when I select items on the listbox.

Is there some special property of the listbox or OLE Container thingy that I need to set to enable this event code to run? I cannot seem to figure out why the event code will not run.

 

[RoryA]

If you view the code module for the sheet containing the Click event and the Listbox, does Listbox1 appear in the top left dropdown of the main code window?

 

[NigelTufnel]

The good news is that it looks like I solved the problem...the bad news is that I'm still not sure what's going on with the "click" event.

To answer your question...Yes...when I create a ListBox1 item on the sheet, the drop-down list on the worksheet code module does show ListBox1.

When I select ListBox1 from the drop-down, it moves the cursor to the beginning of that routine...yet it doesn't run the code when I click on the list box.

On the bright side, it looks like I've come up with a solution.

Apparently, if I change the name of the subroutine from

Private Sub ListBox1_Click()
  MsgBox ("First List Box Clicked")
End Sub

to

Private Sub ListBox1_Change() 
  MsgBox ("First List Box Clicked")
End Sub

then the routine runs fine. And to be honest...it's the change event that I'm really interested in here. I just didn't realize the ListBox had a change event.

Although I'm in good shape now, it's still a bit puzzling. Could it be that ListBoxes don't have a "click" event? That would seem odd, as I generated the declaration for the click event code by double-clicking on the box when in the worksheet Developer "design mode."

Anyway, if you have any thoughts, feel free to let me know. In the meantime, it looks like my problem is solved...and I appreciate your helping me.

 

[RoryA]

Listboxes do have a click event but it doesn't fire for Multiselect lists.

 

[NigelTufnel]

Such knowledge is what separates the Excel MVP from us Excel "pretenders."

 

[beer45]

Hi Rory,

In my vBa version the click event fires when the listbox<>empty.

 

[RoryA]

Really? Which version is that? (it is a multiselect listbox?)