Help with List Box

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.

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

Please Login or Register  to view this content.

sincerely,
Leith Ross

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

--

Dave Peterson

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?

"Leith Ross" wrote:

>
> 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
>
> --------------------
>
>
> sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=556752
>
>

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

ZipCurs wrote:
>
> 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.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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson