PDA

View Full Version : Version clash



dianahowe
07-20-2007, 05:29 AM
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 :help

Bob Phillips
07-20-2007, 05:43 AM
Just use



ActiveSheet.Protect PassWord:=PassWd

dianahowe
07-20-2007, 05:49 AM
That won't allow formatting of columns and cells now which is my problem.

Bob Phillips
07-20-2007, 06:40 AM
if Val(Application.Version) >= 10 Then
ActiveSheet.Protect PassWord:=PassWd, AllowFormattingColumns:=True, AllowFormattingCells:=True
Else
ActiveSheet.Protect PassWord:=PassWd
Endif

dianahowe
07-20-2007, 08:44 AM
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
07-20-2007, 09:06 AM
No, because as YOU pointed out, it isn't supported in previous versions.

rory
07-23-2007, 07:20 AM
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
07-24-2007, 06:31 AM
Thanks Rory, I'll see what I can do with that. :friends: