ListBox on a worksheet from Forms Controls Toolbox

[krishnak]

Hi All,

In the attached workbook, I have a ListBox (ListBox2) and a button (Button3). The ListBox is multi-select; hence the items selected cannot be displayed in the Link Cell.
The button operates a macro ListChoices(). This should display the items selected in the ListBox2.
When I operate the button , I get the error message:

Variable Not defined.

How do I ensure that the name List Box 2 is recognized by the code?
I do not see it in the window on the VB Editor. Changing the name in the Worksheet Name Box to lstBox did not help.

Thanks for any suggestion.

[p45cal]

[VBA]Sub ListChoices()
Dim i As Integer, cnt As Integer
With ActiveSheet.ListBoxes("List Box 2")
'Loop through the items to check whether selected.
For i = 1 To .ListCount
If .Selected(i) Then
Debug.Print .List(i)
End If
Next i
End With
End Sub
[/VBA]

[krishnak]

Thanks p45Cal.

I did not check the box for "Microsoft Forms 2.0 Object Library" in the Tools - Reference menu of the VB Editor. That was the reason the code was failing to see the List Box.
Thanks again.

[p45cal]

Thanks p45Cal.

I did not check the box for "Microsoft Forms 2.0 Object Library" in the Tools - Reference menu of the VB Editor. That was the reason the code was failing to see the List Box.
Thanks again.

??!!
It doesn't need to be checked.

[krishnak]

If I do not check the "MS Forms 2.0 Object Library " reference, I cannot declare the variable as ListBox. If I declare the variable as ListObject, error message results.

I added a command button "Button 3" on the worksheet. In the same macro, I declared another variable btnTest. And I get an error for the following statement:

Set btnTest = ActiveSheet.CommandButtons("Button 3")

The error message is :

"Object does not support this property or method."

[mikerickson]

The ListBox and Button collections are depreciated. Try delcaring them as Shapes.

Dim btnTest As Shape
' ...
Set btnTest = ActiveSheet.Shapes("Button 3")

[krishnak]

No luck. Here is the code I have.
The last two statements cannot be executed. I get the identical error:
"Object does not support this property or method."

[VBA]Sub CheckItems()
Dim i As Integer, cnt As Integer
Dim lstBox As Shape
Dim btnTest As Shape
Set lstBox = ActiveSheet.Shapes("List Box 1")
Set btnTest = ActiveSheet.Shapes("Button 3")
btnTest.Font.Bold = True
MsgBox (lstBox.ListCount)
End Sub[/VBA]

[p45cal]

If I do not check the "MS Forms 2.0 Object Library " reference, I cannot declare the variable as ListBox.

In my xl2007 I can, and this code works without the reference checked:
[vba]Sub ListChoices2()
Dim i As Integer, cnt As Integer, xxx As ListBox
Set xxx = ActiveSheet.ListBoxes("List Box 2")
With xxx
'Loop through the items to check whether selected.
For i = 1 To .ListCount
If .Selected(i) Then
Debug.Print .List(i)
End If
Next i
End With
End Sub
[/vba]

If I declare the variable as ListObject, error message results.

That's because the Listbox has nothing to do with ListObjects, which are Tables on a sheet.

I added a command button "Button 3" on the worksheet. In the same macro, I declared another variable btnTest. And I get an error for the following statement:

Set btnTest = ActiveSheet.CommandButtons("Button 3")

The error message is :

"Object does not support this property or method."

That's because ActiveSheet doesn't support CommandButtons, it does however, support Buttons.
[vba]Sub ListChoices()
Dim i As Integer, cnt As Integer, xxx As ListBox, btnTest As Button
Set btnTest = ActiveSheet.Buttons("Button 3")
btnTest.Select
Set xxx = ActiveSheet.ListBoxes("List Box 2")
With xxx
'Loop through the items to check whether selected.
For i = 1 To .ListCount
If .Selected(i) Then
Debug.Print .List(i)
End If
Next i
End With
End Sub
[/vba]
Again, the MS Forms 2.0 Object Library reference doesn't need to be checked.

Having said that after messing about you may find it hard to remove, it's probably doing no harm but you can remove it programmatically with the following in a module of the relevant workbook (adapted from someone else's code):[vba]Sub removeRef()
Dim wb As Workbook
Dim oRefs As Object ' References
Dim oRef As Object ' Reference
Dim sDes As String

Set wb = ThisWorkbook
Set oRefs = wb.VBProject.References
For Each oRef In oRefs
sDes = oRef.Description
Debug.Print oRef.Name, sDes
If InStr(sDes, "Microsoft Forms") Then
oRefs.Remove oRef
End If
Next
End Sub
[/vba]The above code, in more recent versions of Excel requires trusted access to the vba project:
Office button, Excel Options…, Trust Center, Trust Center Settings…, Macro Settings, check the box "Tust Access to the VBA project object model", and it's safer to uncheck this box later.

[krishnak]

Thanks, this works without the "Forms 2.0 Object Library" selected.

However, whenever I want to assign the data types during declaration stage, the Intellisense does not show 'ListBox' and 'Button' in the drop down boxes.
Any reason for that?

[p45cal]

Any reason for that?

If mikerickson is right and they're being deprecated then that's possibly why.

If you're going down the declaring-them-as-shapes route, then:[vba]Dim lstBox As Shape
Dim btnTest As Shape
Set lstBox = ActiveSheet.Shapes("List Box 2")
Set btnTest = ActiveSheet.Shapes("Button 3")
btnTest.DrawingObject.Font.Bold = True 'or:
btnTest.OLEFormat.Object.Font.Bold = False 'and probably more ways.

MsgBox lstBox.DrawingObject.ListCount 'or
MsgBox lstBox.OLEFormat.Object.ListCount 'likewise: probably more ways.
[/vba]