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" wrote:

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


ZipCurs wrote:

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.ListB ox.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.

--

Dave Peterson