Listbox Displays Odd Data On First Use Then Data as Expected

[wsnyder]

Hi all,
I have a UserForm that Loads and Shows when the Workbook is opened.
The User is given the current date setting that is saved to a worksheet cell on each use.
Ths User is then given a choice to use a different date Yes or No.
If the User chooses Yes, then a Listbox on the UserForm is populated with a list of dates from an Excel Table.

This is where I see some strange results.
One time nothing was displayed.
Another time, dates were dispalyed, but they were not in the correct order as the Excel Table.
It seems like a wild guess when I close the workbook, re-open the workbook and click Yes.

However, if I subsequently raise the form, the dates are displayed in the Listbox as expected.

Have you seen this behavior before?
Any ideas how to defeat this?

Thanks,
-w

Private Sub cmdYes_Click()

'Get user selected values from listbox

'objects
    Dim wb As Workbook
    Dim wsCriteria As Worksheet
    Dim lo As ListObject
    Dim rng As Range
    
    Set wb = ThisWorkbook
    Set wsCriteria = wb.Worksheets("Criteria")
    Set lo = wsCriteria.ListObjects("tblDates")
    Set rng = lo.ListColumns(2).Range.Offset(1, 0)
    Set rng = rng.Resize(rng.Rows.Count - 1, 1)
    Debug.Print rng.Address

'Populate combo box on user form
    With Me
        .lstReportDates.RowSource = rng.Address
    End With

'tidy up
    Set rng = Nothing
    Set lo = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub

 

[dmt32]

Hi
see if this update to your code resolves the issue

Private Sub cmdYes_Click()
    
    'Get user selected values from listbox
    
    'objects
    Dim wb          As Workbook
    Dim wsCriteria  As Worksheet
    Dim lo          As ListObject
    Dim rng         As Range
    
    Set wb = ThisWorkbook
    Set wsCriteria = wb.Worksheets("Criteria")
    Set lo = wsCriteria.ListObjects("tblDates")
    
    Set rng = lo.ListColumns(2).DataBodyRange
    
    Debug.Print rng.Address
    
    'Populate combo box on user form
    With Me.lstReportDates
        .ColumnHeads = True
        .RowSource = "'" & wsCriteria.Name & "'!" & rng.Address
    End With
    
    'tidy up
    Set rng = Nothing
    Set lo = Nothing
    Set ws = Nothing
    Set wb = Nothing
    
End Sub

Dave

 

[wsnyder]

Thanks Dave,

I only changed .ColumnHeads = False.

Looks good so far, I'll keep an eye on it.

Thanks
-w