Help with List Box

[Guest]

I am having a great deal of difficulty doing what appears to be a simple
task. I want to add a List Box from the Controls Toolbox and then set some
variables. I am using the following code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=0, Top:=0, Width:=150, Height:= _
200).Select

With ActiveSheet.ListBox1
.ListFillRange = "F1:F10"
.Font.Size = 14
.MultiSelect = fmMultiSelectMulti
.Font.Name = "FuturaBlack BT"
End With

This will not run in a single macro or continuously. I can get it to work
if a separate macro with the variables is manually started after the List Box
has been put in place. When I do this, the List Box is not active until I
activate and deactivate the Design Tool on the Controls Toolbox. I have
tried putting in DoEvents in the macro and in functions and I am running with
UpdateSheets=True. I do not want the code to run when I click on the List
Box.

 

[Leith Ross]

Hello Zipcurs,

I won't bore you with the details of all that is happens when a object
is embedded. You simply need to modify your code to gain access to the
objects properties. Here is the modified code...


Code:
--------------------

Sub AddListBox()

Dim LB As Object

Set LB = ActiveSheet.OLEObjects("ListBox1").Object

With LB
.ListFillRange = "F1:F10"
.Font.Name = "Arial"
.MultiSelect = fmMultiSelectMulti
.Font.Name = "FuturaBlack BT"
.Font.Size = 14
End With

End Sub

 

[Dave Peterson]

I like to pick out a range and then plop the control on top of that range:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("g12:h18")
End With

With myRng
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With

'you can even name it in code
With OLEObj
.Name = "lb_" & .TopLeftCell.Address(0, 0)
.ListFillRange = ActiveSheet.Range("F1:F10").Address(external:=True)
With .Object
.Font.Size = 14
.MultiSelect = fmMultiSelectMulti
.Font.Name = "FuturaBlack BT"
End With
End With

End Sub

 

[Guest]

Thank you for a big step in the right direction. The code you gave me
wouldn't run exactly as supplied. For some reason, I could not set all of
the variables with the same object. Although it may not be technically
correct for the LB case, it worked. I ended up using:

Dim LB As Object
Dim LBP As Object

Set LB = ActiveSheet.OLEObjects("ListBox1")
Set LBP = ActiveSheet.OLEObjects("ListBox1").Object

With LB
.ListFillRange = "F1:F10"
.Enabled = True
.AutoLoad = True
End With

With LBP
.Font.Size = 14
.MultiSelect = fmMultiSelectMulti
.Font.Name = "FuturaBlack BT"
.BorderStyle = fmBorderStyleSingle
End With

I still have the persistent problem of the list box not being accessible
until I go into design mode. Any thoughts on this?

 

[Guest]

Thank you Dave,

This works and the nested With statements are much more intuitive. The List
Box still does not work. I have gotten it to work by selecting another sheet
and then coming back. This seems lame, but it works. DoEvents in a function
does not.

Any thoughts.

 

[Dave Peterson]

Sometimes the listbox didn't work for me.

But I could click on the design mode icon (twice) and it brought it to life.

But this worked (with a bit of a flicker):

Option Explicit
Sub testme2()

Dim OLEObj As OLEObject
Dim myRng As Range
Dim wks As Worksheet
Dim OtherWks As Worksheet

Set wks = ActiveSheet
Set OtherWks = Worksheets(2)

With wks
Set myRng = .Range("g12:h18")
End With

With myRng
Set OLEObj = .Parent.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With

'you can even name it in code
With OLEObj
'.Name = "lb_" & .TopLeftCell.Address(0, 0)
.ListFillRange = wks.Range("F1:F10").Address(external:=True)
With .Object
.Font.Size = 14
.MultiSelect = fmMultiSelectMulti
.Font.Name = "FuturaBlack BT"
End With
End With

OtherWks.Select
wks.Select

End Sub

Change that otherwks to point at a different worksheet.