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.

 

[Patriot2879]

No, you haven't attached anything.

I just need to know what the value of the variable is. Add a line before the Rowsource line that is just:

Msgbox lr

and run the code again and tell me what the message says.

Hi is the image attached now. it doesnt work if i put 'msgbox 1r' infront of .RowSource = "Data_Display!A2:K" & lr

 

[RoryA]

Its LR not 1R

 

[Patriot2879]

Its LR not 1R

aww sorry, the message box said number '19' hope this helps.

 

[RoryA]

OK, so the range address is valid. Is that sheet in the active workbook when the code runs?

 

[Patriot2879]

OK, so the range address is valid. Is that sheet in the active workbook when the code runs?

It is yea

 

[RoryA]

There's nothing wrong with the code itself, so there must be something else at work. Are there any strange values in the data - eg errors or really long text? Also, how is this code being triggered?

 

[Patriot2879]

There's nothing wrong with the code itself, so there must be something else at work. Are there any strange values in the data - eg errors or really long text? Also, how is this code being triggered?

Hi he code gets triggered when someone puts in the date in a textbox

 

[RoryA]

I'd have to see the workbook, I think. As I said, the code you have posted is absolutely fine.

 

[Patriot2879]

I'd have to see the workbook, I think. As I said, the code you have posted is absolutely fine.

Hi good morning, i have attached a link to the file via dropbox, hope this works and that you can help. the error does not happen every time, it seems to happen if the file is not used for a while I think, thank you - please click the link below
error 380

 

[Patriot2879]

Hi good morning, i have attached a link to the file via dropbox, hope this works and that you can help. the error does not happen every time, it seems to happen if the file is not used for a while I think, thank you - please click the link below
error 380

Hope you’re ok to have a look at this please? And can still help. Much appreciated