Version clash

[dianahowe]

I have written a macro which has been distributed throughout my company for different units to use.

It contains the following instruction:

ActiveSheet.Protect PassWord:=PassWd, AllowFormattingColumns:=True, AllowFormattingCells:=True

Which works fine for the majority. However, a couple of units have older versions of Excel, where AllowFormattingColumns and AllowFormattingCells are not part of the Protect method and therefore throws up an error.
Is there another way to protect sheets while still allowing format columns and cells in the older versions of Excel or should I just tell them to make business cases for newer versions?

Thanks in advance for your

[Bob Phillips]

Just use

[vba]

ActiveSheet.Protect PassWord:=PassWd
[/vba]

[dianahowe]

That won't allow formatting of columns and cells now which is my problem.

[Bob Phillips]

[vba]

if Val(Application.Version) >= 10 Then
ActiveSheet.Protect PassWord:=PassWd, AllowFormattingColumns:=True, AllowFormattingCells:=True
Else
ActiveSheet.Protect PassWord:=PassWd
Endif
[/vba]

[dianahowe]

No, I'm not sure I'm getting you to understand the problem.

What I want to know is, for the older versions of Excel, is there something I can do which will allow formatting of cells on a protected sheet. Just getting them protected I'm okay with - it's the formatting which is an issue.

[Bob Phillips]

No, because as YOU pointed out, it isn't supported in previous versions.

[rory]

If you can't get them an upgrade, you'll probably have to add your own functionality to format the columns. If you use the userinterfaceonly:=true argument when protecting the sheet, your code won't even need to unprotect the sheets to format them.
HTH
Rory

[dianahowe]

Thanks Rory, I'll see what I can do with that.