Userform - Listbox - not populating table properly

[Kawule]

So I've been working on my userform and today I added in a new page to my multipage and encountered a new problem after implementation.

What I've found is that 75% of the time the Listbox's rowsource has no value and 25% of the time it seems to keep the value. The data it pulls from is on a hidden sheet named "Operators" with a named range called "OprTbl"
Here is the snippet of the code I placed in the UserForm_Initialize section:

Private Sub UserForm_Initialize()
MultiPage1.Value = 0
'Populate the Approval page
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Operators")
Set ws2 = Worksheets("Approval")
'This populates the listbox with the operator table
With Me.OperatorsListBox
    .RowSource = ws.Range("OprTbl").Address
End With
'Sets all the label captions from the approval table
Me.AprLbl.Caption = ws2.Range("a2")
Me.AppLbl.Caption = ws2.Range("b2")
Me.EffDateLbl.Caption = ws2.Range("c2")
Me.ExpDateLbl.Caption = ws2.Range("d2")
Me.AprHdrLbl.Caption = ws2.Range("e2")
End Sub

When I was debugging it I noticed that after the End With .RowSource becomes empty instead of being assigned the named range that I setup.

 

[Norie]

Why not just set RowSource to 'OprTbl'?

 

[Kawule]

Do you mean just putting in

Me.OperatorsListBox.RowSource = Range("OprTbl").Address

Instead of having it inside the With statement? I tested with the Me.OperatorsListBox.RowSource = Range("OprTbl").Address only and still running into the same issue the list box won't populate

 

[Norie]

No, all you should need is this.

Me.OperatorsListBox.RowSource = "OprTbl"

 

[Kawule]

Well that worked thanks! I dunno why it originally worked when I used the "With" statement and then it broke immediately after that but your solution is a lot simpler.

 

[Norie]

In the first code you posted the sheet reference would have been missing.