Not getting the result I was hoping for

[Patrick C. Simonds]

This code is using the current (active) Worksheet to provide the list data
not Friday Workshifts. Can any one tell me what I have missed?



Private Sub UserForm_Initialize()
With Worksheets("Friday Workshifts")
ListBox1.RowSource = "$A$2:$R$200"
End With
End Sub

 

[Mike]

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim curCell As Variant
Dim r As Long
Set ws = Worksheets("Friday Workshifts")
ListBox1.Clear
For r = 2 To 200
Set curCell = ws.Range("A" & r)
If Not IsEmpty(curCell) Then
ListBox1.AddItem (curCell)
End If
Next
End Sub