macro to change number format of a highlighted range in Excel 2007

[Lauren977]

I was using the following macro to change the number format of a highlighted
range.

Selection.NumberFormat = "#,##0"

In Excel 97 and 2000 it was working fine. In Excel 2007 it is giving me the
following error message:

Run-time error '1004'
Unable to set the NumberProperty of the range class

I am stumped and haven't been able to find any documentation on this change.

 

[Dave Peterson]

Is the worksheet protected?

I was using the following macro to change the number format of a highlighted
range.

Selection.NumberFormat = "#,##0"

In Excel 97 and 2000 it was working fine. In Excel 2007 it is giving me the
following error message:

Run-time error '1004'
Unable to set the NumberProperty of the range class

I am stumped and haven't been able to find any documentation on this change.

 

[Lauren977]

Hi Dave,

Yes the worksheet is protected, but I am running a routine to remove the
protection. This routine is the same as in my other macros and is working
there:

If ActiveSheet.Protect = True Then ActiveSheet.Unprotect Password:="percy"

Thanks for getting back to me quickly.

Lauren

 

[Dave Peterson]

If you want to check for protection, you should use something like:

With ActiveSheet
If .ProtectContents = True _
Or .ProtectDrawingObjects = True _
Or .ProtectScenarios = True Then
.Unprotect Password:="percy"
end if
end with

But I bet excel will forgive you if you just unprotect it without checking:

ActiveSheet.Unprotect Password:="percy"

 

[Lauren977]

Thanks Dave,

That works great! You have saved me a huge amount of trial and error.

Lauren