Problem with .AddItem to Listbox

[Ginny2222]

I want to populate a listbox with the data in column B dynamically. So I am adding the following code in the userform initialisation. It stops at the .AddItem cellValue line. My listbox value is appearing as nothing.

Dim r As Long
Dim RequiredRosterDWSText As MSForms.ListBox
Dim numValsToAdd As Long
Dim cellValue As String


numValsToAdd = Application.WorksheetFunction.Count(Sheet2.Range("A:A"))
With RequiredRosterDWSText
For r = 2 To numValsToAdd
cellValue = Sheet2.Range("B" & r)
.AddItem cellValue
Next r
End With

Can anyone tell me what I am doing wrong!!

Help appreciated.
Ginny

 

[HaHoBe]

Hi, Ginny,

if it´s code to be used on a UserForm the following code should do the work (no need to Dim a ListBox there):

Dim r As Long
'Dim RequiredRosterDWSText As MSForms.ListBox
Dim numValsToAdd As Long
Dim cellValue As String


numValsToAdd = Application.WorksheetFunction.Count(Sheet2.Range("A:A"))
With RequiredRosterDWSText
  For r = 2 To numValsToAdd
    cellValue = Sheet2.Range("B" & r)
    .AddItem cellValue
  Next r
End With
End Sub

Ciao,
Holger

 

[Ginny2222]

Thanks Holger - works a dream. I can't believe that was all that was wrong!

rgs
Ginny