2007 compatibility

[burtburt]

Well I found another issues. 2003 xls sheets with cells that have validation loose there validation when opened in Excel 2007.

This sucks, I had to rebuild them all.

If that's not enough, the whole save as interactive web page is gone in 2007. They want us all to purchase MOSS 2007 + (Excel Servicesfor $25/user/year). Sounds cheap until you start multiplying.

[Bob Phillips]

Are you sure? I haven't heard this, and so I just tried it. Just a simple list DV I admit, but no problems here.

Are there some particular situation where it happens?

[CaptRon]

I just opened up one of my workbooks built in Excel 2003 and all the data validation seems to work OK in 2007. The 3 types I checked were list, whole numbers, and dates.

Which type if DV was not working for you?

Ron

[mdmackillop]

Conditional Formatting
In 2007, cel.FormatConditions(1).Formula1 will return a string like "=2"
previously this was returned as the simple value.

Use Evaluate to get the numeric value
[VBA]
With cel.FormatConditions(1)
myColour = .Interior.ColorIndex
myOp = .Operator
myVal = Evaluate(.Formula1)
End With
[/VBA]

[burtburt]

Ok. I didn't communicate my issue correctly. What I should have said was that to change a validation message in 2007 you need to first delete the old validation (if it exists). In 2003 this worked without deleting it:

[VBA]

Cells(intRow2Write, 7).Validation.InputMessage = strGateCrit

[/VBA]

Now I have to delete first then add it from scratch including the validation and the validation message.

[VBA]
With Cells(intRow2Write, 7).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$1:$G$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Criteria:"
.ErrorTitle = ""
.InputMessage = strGateCrit
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
[/VBA]

Sorry about the confusion.

[Bob Phillips]

I have just changed the InputMessage in 2007 without deleting the validation. Seems the same to me.

[burtburt]

Well I guess I'm loosing my credibility fast here. I don't know why I'm having these issues and no one else is. Perhaps it has something to do with creating the validation in an xls in 2003, and trying to update the message on the xls book in 2007. Regardless, I'm going to drop it. "Don't fix it if it ain't broke", my grandpa used to say. Thanks for the ear...

[Paul_Hossler]

Has anyone experienced formula errors when saving a 2007 WB as a 2003?

A customer has mandated that no one use any office 2007 product due to errors. This includes back-saving Excel 2007 as 2003 due to "formula errors in the 2003 WB."

Google does not locate any articles that seem to address bad formulas being generated

Has anyone heard of this issue?

Paul

[Bob Phillips]

We have seen disappearing range names, but that was fixed by a hotfix a couple of months back.

[ProteanBeing]

An issue I found was with the sort method. I recorded a macro in 2007 and it will not function in 2003 because the sort method has changed. My guess is that this will happen with a lot of recorded macros.

[Bob Phillips]

But a sort macro recorded in 2003 will work in 2007.Moral of the story, develop on the lowest version.

[geekgirlau]

Often the problem with recorded macros is new/changed parameters. It's always a good idea to clean up recorded code to remove the stuff you don't need - if you don't need to change a specific parameter and the macro recorder has just inserted the default value, get rid of it!

[kestrylr]

Thanks for all the tips here! I'm currently still in 2003 at work, but some of the people I support have switched to 2007 and I can already see the headaches piling up on the horizon...

(It's a VERY large company - I have no say in when my computer will be upgraded... )

[Simon Lloyd]

Thanks for all the tips here! I'm currently still in 2003 at work, but some of the people I support have switched to 2007 and I can already see the headaches piling up on the horizon...

(It's a VERY large company - I have no say in when my computer will be upgraded... )

Trust me, you'll hate the move to 2007, just think of it as if someone swapped your hands over, rearranged the fingers and then turned them back to front and you are trying to thread a needle!

I have a true dislike for it, i've moved on to 2010 and it seems a tad better although everything still seems misplaced for me, still hard to get used to.

[geekgirlau]

It's a HUGE learning curve - it's the first time in years that I've really had to hunt to find things in an Office product upgrade. I have seen some tools rolled out whereby you can go through a faux Office 2003 screen to select an option, and it shows you where the corresponding option lives in 2007.

Getting used to it now (and I do like some of the new features) but my biggest frustration is with the ribbon. Their policy appears to be "I'm going to guess what I think you want to do next, and make the ribbon that you actually want (and hunted for 5 minutes to find) disappear again". I'd love to switch this off (if I could work out how to do it) - contrary to Microsoft's opinion I generally have a slightly better idea of the functions I need to access next then they do!

[Simon Lloyd]

........ - contrary to Microsoft's opinion I generally have a slightly better idea of the functions I need to access next then they do!

Have you told them this? they probably have never heard that before

Heaven forbid that they actually listen to their client base and make you a valued part of the development in creating something that we (the collective frustrated office users "We") can actually use to perform our task efficiently!

[Charlize]

I have a true dislike for it, i've moved on to 2010 and it seems a tad better although everything still seems misplaced for me, still hard to get used to.

Know the feeling ... Bosses wanted to go from 2007 (finally everyone was used to it and most things worked back (after some sleepless nights) to 2010 because they have the option for included upgrades.

Sometimes I think, although I know I don't know everything, how hard it must be for my fellow collegues if even I have some difficulties to grasp everything that has been changed.

Charlize

[mdmackillop]

I found an issue with using Listbox.Selected(i) in 2010 to test for any selected items. In the end I came up with this to be compatible between 2003 & 2010

[VBA]'Check for Selected; handle 2003 and 2010 versions
If IsNull(ListBox1) And ListBox1.Selected(1) = False Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If[/VBA]

[Bob Phillips]

Why not test ListIndex?

[mdmackillop]

Thanks Bob. I new I was missing a trick!