Unspecified error trying to load Listbox

[sharky12345]

I am loading a Listbox on a Userform like so;

With Me.ListBox1
.ColumnCount = 10
.ColumnHeads = True
.ColumnWidths = "40;50;110;60;55;60;60;60;0;0"
.RowSource = Sheet1.Range("A2:M" & LastRow).Address(, , , True)
End With

Most of the time it works fine, but regularly it gives me an error when I try to load it, this comes after I have changed sheet values - the error is simply;

2147467259 (80004005) Could not set the rowsource property - Unspecified Error

I have a possible solution suggested after a Google search which is to set the Listbox to load from an array but I have no idea how to do this if someone can help?

 

[RoryA]

Replace this line:

.RowSource = Sheet1.Range("A2:M" & LastRow).Address(, , , True)

with this:

.List = Sheet1.Range("A2:M" & LastRow).Value

 

[sharky12345]

Thanks Rory, but same error is occurring after I've tried loading the form a few times.

It's odd, because it works fine for say half a dozen times then the problem occur - if I comment the Listbox part of the userform initialize then it works fine so must be something to do with that.

 

[Dryver14]

sharky, looking into this it seems on the second run of load it is not collecting any added lines etc.

Lookup listbox requery. I think it may solve your problem

 

[RoryA]

I assume the error message is slightly different since you can't get a Rowsource error when you aren't using the RowSource property.

 

[sharky12345]

Slightly different, yes - now it says could not set the List property but same error codes of 2147467259 and (80004005).

Haven't had time yet to look at what Dryver14 has suggested....