Use ListBox properties in This Workbook

[cristio26]

Hi guys,

Can anyone help me please. I have a small problem :

I'm trying to use ListCount property of an ListBox in This Workboo
object of the workbook and I get run-time error '424' Object requierd.
The script is the following:

Private Sub workbook_open()

Dim d3 As Integer
Dim d4 As Integer
d3 = ListBox1.ListCount - 1


For d4 = 0 To d3
With Sheets("WERA Scorecard").Range("B35:B100")
Set D = .Find(ListBox1.List(d4), LookIn:=xlValues
LookAt:=xlWhole)
If Not D Is Nothing Then
ListBox1.Selected(d4) = True
Else
End If
End With
Next d4

End Sub


I'm not a programmer so I just read help and try to figure it out bu
this time I didn't manage to find the solution.

I would be really happy if somebody can help me with this issue.

Thanks

 

[Tom Ogilvy]

Put your code in a general module and call it from the Workbook_Open Event

Sub ProcessListbox()
Dim d3 As Integer
Dim d4 As Integer
Sheets("WERA Scorecard").Activate
d3 = ListBox1.ListCount - 1

For d4 = 0 To d3
With Sheets("WERA Scorecard").Range("B35:B100")
Set D = .Find( Sheets("WERA Scorecard"). _
ListBox1.List(d4), LookIn:=xlValues, _
LookAt:=xlWhole)
If Not D Is Nothing Then
Sheets("WERA Scorecard").ListBox1.Selected(d4) = True
End If
End With
Next d4

End Sub

Private Sub Workbooks_Open()
ProcessListBox
End sub