run time error 13

[GlennLGillies]

Hi all.

I did do a "site:MrExcel.com..." search before I posted this. Found that you have helped many problems with it, but I could not find an answer to mine. I hope you can help.

(I have been using this file for some time and now it suddenly decides to give me an error). I have deleted the module and form, wrote them afresh but nothing is happening. I am getting "run time error 13, type mismatch".

Here is the code...

Sub ShowUserFormListBox()
UserFormListBox.Show ' This is the line that gets highlighted
End Sub

Can anyone point me in the right direction?
Thank you in advance
Glenn

 

[Ruddles]

What is UserFormListBox? A user form or a list box?

If it's a user form, did you create it with the default name of UserForm1?

 

[GlennLGillies]

Uhm, Yes I did (all of a sudden I am feeling quite small!?)

Glenn

 

[Ruddles]

http://xkcd.com/482/

 

[Norie]

Glenn

In the VBE goto Tools>Options and on the General tab select the Break On All Errors option.

Now run the code again and the debugger should goto the line of code that's really the problem.

It's most likely somewhere in the userform module, perhaps in the Initialization sub.

 

[GlennLGillies]

Yes, followed your instructions and here is where the code is highlighted (remember this has been working fine for some months now, why would it suddenly go haywire?

Private Sub UserForm_Initialize()
Dim MyUniqueList As Variant, i As Long
With Me.ListBox1
.Clear ' clear the listbox content
MyUniqueList = UniqueItemList(Range("AF14:AF1000"), True)
For i = 1 To UBound(MyUniqueList) " This is the highlighted line!
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
End Sub

Thank you

Glenn

 

[Norie]

Glenn

I think you'll need to delve further into this, in particular the UniqueItemList sub/function.

Before you do that set a breakpoint (F9) just before the problem line.

Now when the code stops add a Watch on MyUniqueList to find out what it is.

It should be an array as far as I can see and it if it's not that's probably what's causing the problem.

So you'll need to find out why it isn't an array by looking at the Unique... function.

You should also check the data as well - if this has been working without error it could be a problem there rather than in the code itself.