Drop down list and macro work excel 2010 but not 2007?

Afternoon all,

I've designed a spreadsheet in excel 2010 that has a dropdown list and a macro attached to a button. These work fine in office 2010 but when another user opens the file using excel 2007, the macro encounters an error and the dropdown arrow has disappeared.

Any suggestions what I can do to resolve this?

I've tried changing the macro setting in excel but this didn't work.

Not sure if this is the correct forum but seeing as a macro was involved I thought I'd try here first!

Thanks.

Has anyone got any suggestions?

Thanks.

Please Login or Register  to view this content.

Hi Roy, the code above is a bit of the code I'm using. It's freezing up over the xlPivottable14 part - I'm presuming this is not compatible with Excel 2007 but I'm not sure how to get around it.

I'm a beginner in writing VBA code so forgive me if the above is extremely inefficient.

As for the drop down list, I can't really paste the whole file as there is a lot of confidential info in there and I can't just send part of it as the file wouldn't work!

How can I find what type of dropdown list it is? When using 2010 there's a drop down arrow to the right of the cell and you select an option referencing a sheet with a list of choices.

Yes, it seems to be data validation. When I open the file using Excel 2007 and look at data validation it does not reference the list, but it does when opened via 2010.

Any way to overcome this? Would it work it I added a list using 2007 and saved the file?

Any thoughts on the problem with the pivot table in the macro code?

Thanks.

I'm having the same issue with data validation btwn 2007 and 2010. I originally created a workbook in 2007, got a new machine w/2010, saved the same wkbook (without touching the data validation) and now users w/2007 can't see the dropdowns!!!

Does anyone have an answer to this?

I just found a work around to this... name the range of cells that the drop-down validation refers to and it should work across excel versions. I can't believe this is required since I sometimes dynamically create such drop downs and will now have to edit significant amounts of code to also dynamically create the named ranges. MS should fix this compatability bug ASAP!!!

I found out that Data validation (including macro's) that work in 2010, don't work in 2007. The validation just dissapeared from the cells...
But I had the List-source in another sheet. When I put everything in Excel 2010 in one sheet, it also works on Excel 2007.
So this might be a solution for the questions above!

But I want to add some rules regarding read-only or other security to the drop-down-lists. So it would be easy if the lists can be filled in another sheet.
Does anybody have an idea?

Regards,
Jorik

I had the same situation with disappearing data validation arrows, except between Excel 2010 and 2003 - my excel sheet created in 2010 didn't appear correct in 2003.

This issue was solved using the solution BGbaum proposed: by using names to delininate my ranges instead of defining the range using something like =$A$12:$A$13. I had success with this even when my named ranges were on a separate worksheet from my data validation lists.