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.

 

[beer45]

You were 100% right.
I used multicolumn.
Sorry for the confusion.

bart

Private Sub ListBox1_Change()
'fired
End Sub


Private Sub ListBox1_Click()
Stop
End Sub




Private Sub ListBox1_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)
Stop    '?
End Sub


Private Sub ListBox1_GotFocus()
Stop
End Sub


Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'fired
End Sub


Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'fired
End Sub


Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'fired
End Sub


Private Sub ListBox1_LostFocus()
Stop
End Sub


Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub


Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub


Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub

 

[beer45]

Hi RoryA

100% right you were!!!!

Private Sub ListBox1_Change()'fired
End Sub


Private Sub ListBox1_Click()
Stop
End Sub


Private Sub ListBox1_Error(ByVal Number As Integer, ByVal Description As MSForms.ReturnString, ByVal SCode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, ByVal CancelDisplay As MSForms.ReturnBoolean)
Stop    '?
End Sub


Private Sub ListBox1_GotFocus()
'fired
End Sub


Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'fired
End Sub


Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'fired
End Sub


Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'fired
End Sub


Private Sub ListBox1_LostFocus()
'fired
End Sub


Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub


Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub


Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
'fired
End Sub

 

[slopok]

RoryA,

This was extremely helpful. I was searching for almost 2 days for some code that could create the listbox on the sheet & give it multiple values as well as the multiselect capability. I was running into a bunch of halfway code that did not work. Yours did the trick. Thank you so much!!!


Just in case anyone else needs to list the multiple choices the user makes in the list box and put those on the spreadsheet... you can use this in the sheet's module:

Sub ShowListBoxMultipleValuesOnSheet()

Dim exhaustiveCounter As Integer, selectedCounter As Integer
Dim strItem As String

selectedCounter = 0
For exhaustiveCounter = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(exhaustiveCounter) = True Then
strItem = ListBox1.List(exhaustiveCounter)
Range("A1").Offset(selectedCounter, 0).Value = strItem
selectedCounter = selectedCounter + 1
End If
Next exhaustiveCounter

End Sub

 

[slopok]

RoryA,

It thank you for posting your code on creating an ActiveX listbox on a spreadsheet... super extremely helpful!!! I was searching for almost 2 days for some code that could create the listbox on the sheet & give it multiple values as well as the multiselect capability. I was running into a bunch of halfway code that did not work. Yours did the trick. Thank you so much!!! Here is my copy cat code, with names modified:

Sub CreateOriginalListBox()
'This was difficult to get to work... succeeded because RoryA had...
'http://www.mrexcel.com/forum/excel-questions/481041-trouble-using-visual-basic-applications-create-activex-listbox-worksheet.html


On Error Resume Next

'Clearing the range where the selected items of the listbox are shown
Range("J:J").EntireColumn.ClearContents

'Creating a new list box (user will be able to select multiple values from the list box' possible values)
Dim source As String
source = "your page name where your data is"
Dim bx As OLEObject
Dim lbx As MSForms.ListBox
Set bx = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Range("B1").Left, Top:=Range("B1").Top, Width:=160, Height:=215)
With bx
.Name = "bx1"
.ListFillRange = "'" & source & "'!" & Range("A1").Address
.Visible = False
.Visible = True
Set lbx = .Object
End With
With lbx
.MultiSelect = fmMultiSelectExtended
.MatchEntry = fmMatchEntryComplete
End With

On Error GoTo 0


End Sub


Just in case anyone else needs to list the multiple choices the user makes in the list box and put those on the spreadsheet... you can use this in the sheet's module:

Sub ShowListBoxMultipleValuesOnSheet()

Dim exhaustiveCounter As Integer, selectedCounter As Integer
Dim strItem As String

selectedCounter = 0
For exhaustiveCounter = 0 To bx1.ListCount - 1
If bx1.Selected(exhaustiveCounter) = True Then
strItem = bx1.List(exhaustiveCounter)
Range("A1").Offset(selectedCounter, 0).Value = strItem
selectedCounter = selectedCounter + 1
End If
Next exhaustiveCounter

End Sub

 

[slopok]

The

'Clearing the range where the selected items of the listbox are shown
Range("J:J").EntireColumn.ClearContents

should be "A:A" instead of "J:J"