additem to listbox

[Lee]

Try to add an item to a ListBox1 on Sheet 5, why got an
error message for using "ControlFormat" below?

Private Sub Worksheet_Activate()
Dim lb as Object
Dim X as Long
With Worksheets(5)
Set lb = .Shapes("ListBox1")
lb.ControlFormat.RemoveAllItems
lb.ControlFormat.AddItem "Default"
For x = 1 To 7
lb.ControlFormat.AddItem Worksheets
("POI").Cells(x, 1)
Next
End With
End Sub

 

[Bob Phillips]

Hi Lee,

Try this alternative

Private Sub Worksheet_Activate()
Dim lb As Object
Dim X As Long
With Worksheets(3)
Set lb = .ListBox1
lb.Clear
lb.AddItem "Default"
For X = 1 To 7
lb.AddItem Worksheets("POI").Cells(X, 1)
Next
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Private Sub Worksheet_Activate()
Dim lb As Object
Dim X As Long
Dim lBox As MSForms.ListBox
With Me
Set lb = .Shapes("ListBox1")
Set lBox = lb.OLEFormat.Object.Object
lBox.Clear
lBox.AddItem "Default"
For X = 1 To 7
lBox.AddItem _
Worksheets("POI").Cells(X, 1)
Next
End With
End Sub

works for me.