User-Defined Type Not Defined

[James_Latimer]

Excel 2010

Hello,
I'm hoping someone can give me a little assistance please.....

I have an xlsm file which seemed to be working fine. I have since recorded a macro for sorting columns descendingly and amended the code putting it into the activate code of one of the sheets.

The code is.....

Private Sub Worksheet_Activate()
    Sheets("D2").Sort.SortFields.Clear
    Sheets("D2").Sort.SortFields.Add Key:=Range("E6:E8"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("D2").Sort
        .SetRange Range("B6:E8")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

    End With
End Sub

The issue i have now is that whenever i type into a cell and hit return i am getting a message "User-defined type not defined". I'm not sure why. I've had a look at other posts and it all seems to be down to references (i cant see any that are missing or unchecked that should be - dont quote me on that!!).

This works fine in excel 2007 though.

If anyone can point me in the right direction it would be much appreciated.

 

[Andrew Poulsom]

What other code do you have in that workbook?

 

[Norie]

The code you posted shouldn't be triggered by a cell change, it should only be triggered when the worksheet is activated.

Is there any other code?

 

[James_Latimer]

This is the strange thing, i dont have anything else as far as coding goes.

No modules, nothing in ThisWorkbook and nothing in the sheets (except of course for the one sheet with the code (above).

One thing to point out is that I added in the coding when the file was simply an excel workbook without macro enabled (xlsx). Dont know if this matters. I am having the same issue on two seperate machines yet it works fine in excel 2007!?!?!

Thanks for the prompt replies!!

 

[Andrew Poulsom]

When the compiler shows the error which line of code is highlighted?

 

[James_Latimer]

Thank you Andrew for having a look!! Very kind of you.

I go onto any worksheet and type in to a cell (any cell), i hit return and the message appears. I okay the message and there is nothing highlighted (generally has the sheet selected that i was inputting in to).

I wish i could tell you something helpful!!!!!

 

[Andrew Poulsom]

In the Visual Basic Editor choose Tools|Options, check 'Break in Class Module' on the General tab and click OK. Now does a line get highlighted when you get the error?

 

[James_Latimer]

I changed it from 'Break on Unhandled Errors' to 'Break in Class Module' as suggested but nothing is highlighted.

Another strange thing is that if i go into the VB side, click into my code and hit the Run button then the coding runs without errors. It is only when making changes on the front end that the message appears?!?!?!

 

[Andrew Poulsom]

It's not that code that's causing the error. It's something else. And it's a compile error so my previous suggestion was a bit of a long-shot. What happens if you choose Debug|Compile VBAProject in the Visual Basic Editor. Oh, and do you have any other workbooks open, including a Personal.xls?

 

[James_Latimer]

Im glad you know what you're talking about!!

If i run Compile VBAProject the option greys out, no errors etc.

On one machine i have a couple of other workbooks open but on a different machine it is just this workbook alone. Both machines getting the errors.

I'm assuming this is not the response you were hoping for!!