run time error 380 rowsource property invalid property value

[Patriot2879]

Hi good morning, Please can you help me, i get a 'Run time error 380 property invalid property value' and it highlights in yellow the line below, but i have no idea how to correct it as i cant see what the issue is

.RowSource = "Data_Display!A2:K" & lr

anf this is the whole code for my listbox -

Sub Refresh_Listbox()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
 
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")

''''''''''' Copy Data ''''''''''
dsh.Cells.Clear
sh.AutoFilterMode = False

If Me.cmb_Filter_By.Value <> "ALL" Then
    sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_Filter_By.Value, sh.Range("1:1"), 0), "*" & Me.txt_Search.Value & "*"
End If

sh.UsedRange.Copy dsh.Range("A1")

sh.AutoFilterMode = False

Dim lr As Long
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 11
    .ColumnWidths = "20,160"
    .RowSource = "Data_Display!A2:K" & lr
End With

End Sub

Really hope you can help me please.

 

[RoryA]

I haven't yet replicated your error. Can you give me the exact steps you take when it occurs?

 

[Patriot2879]

I haven't yet replicated your error. Can you give me the exact steps you take when it occurs?

Hiya it doesn’t happen all the time it seems to happen when the file has been open a while and not used. But it seems to happen when you’re putting in the date. If you open it and fill the form in you won’t get the error, it’s weird as only happens every so often.

 

[Patriot2879]

Hiya i have noticed that the error happens if you have the Userform open and click on another spreadsheet then click back to the Userform which is still open the i get the error, hope you can help me please. the error is '.Rowsource = "Data_Display!A2:K" & lr' please can you help

 

[RoryA]

Your link only refers to a worksheet name, not a workbook name. If you activate a different workbook that doesn't have a sheet by that name, you will get an error. If you need to be able to do that, you need to include the name in the rowsource:

"'[workbook name.xlsm]Data_Display'!A2:K" & lr

is the basic syntax. You need the square brackets and the single quotes.

 

[Patriot2879]

Your link only refers to a worksheet name, not a workbook name. If you activate a different workbook that doesn't have a sheet by that name, you will get an error. If you need to be able to do that, you need to include the name in the rowsource:

"'[workbook name.xlsm]Data_Display'!A2:K" & lr

is the basic syntax. You need the square brackets and the single quotes.

Hi RoryA that seems to be working now thank you for your help