Excel 2007 VBA - Run-time error 1004

[SG]

In Excel 2007, a macro has the following at the beginning of the code:

Range("A4", Range("A4").End(xlDown)).NumberFormat = "$#,##0.00"

When I run the macro, it gives the following error message and the debug
points to this line of code
Run-time error 1004; Unable to set the number format property of the range
class

I do not see any problem with this code. Any help will be appreciated.

I have run the SP1 thinking that it might solve the problem but it has not.

For testing, I removed that line of code and ran the macro, it then again
stopped at the following line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

The error message this time was run-time error 1004 , You cannot use this
command on a protected sheet....

To begin with, the sheet is not protected.

Is there still a flaw in Excel 2007 with VBA macros that has not been taken
care of in SP1 ?
Thanks.

 

[Dave Peterson]

I bet the worksheet is protected.

If your code is behind a worksheet, then those unqualified ranges refer to the
sheet owning the code--not any sheet that you activated.

If this doesn't help, you may want to provide more code and state where the
module is.

 

[Jon Peltier]

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

- Jon

 

[Dave Peterson]

I use that first version lots of times in xl2003 and below.

I fired up VPC and xl2007 to test it with:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Range("A4", Range("A4").End(xlDown))
Debug.Print myRng.Address
End Sub

And got this in the immediate window:
$A$4:$A$19


I put the code in a general module (no qualified ranges!) and it worked ok.

Are you sure it breaks in your version (<bg>) of xl2007?

 

[Jon Peltier]

I tested it in 2003 and in 2007 and it broke. Just now I tested it in both
versions and it worked. I must have typed it wrong in my test module.

Sorry.

- Jon

 

[Dave Peterson]

Whew!

I forgive you <vvbg>.

I tested it in 2003 and in 2007 and it broke. Just now I tested it in both
versions and it worked. I must have typed it wrong in my test module.

Sorry.

- Jon

 

[SG]

Thanks both of you for the suggestions. But what I have discovered here is
what Dave had suggested in the first thread.

Let me give you the details of what I am doing here:

In excel 2007 I am calling for a OLAP cube which has drill-through action
setup in it. Using VBA if i call for the server action, the worksheet created
using drill through is protected and it cannot be unprotected (the area to
unprotect is greyed out). The code I am using to call for the drill through
action is
ActiveCell.ServerActions("driildetail").Execute
This code is in the worheet area where the pivot table with cube data is

If I right click from the pivot table and select the drill-through action
then the sheet created is not protected.
Is there any way the worksheet created from a drill through action be
unprotected programmatically. Activeworkbook.unprotect or
activesheet.unprotect does not work.

Thanks.

SG

 

[Dave Peterson]

Just so you know someone read your message...I have no idea. Sorry.

Thanks both of you for the suggestions. But what I have discovered here is
what Dave had suggested in the first thread.

Let me give you the details of what I am doing here:

In excel 2007 I am calling for a OLAP cube which has drill-through action
setup in it. Using VBA if i call for the server action, the worksheet created
using drill through is protected and it cannot be unprotected (the area to
unprotect is greyed out). The code I am using to call for the drill through
action is
ActiveCell.ServerActions("driildetail").Execute
This code is in the worheet area where the pivot table with cube data is

If I right click from the pivot table and select the drill-through action
then the sheet created is not protected.
Is there any way the worksheet created from a drill through action be
unprotected programmatically. Activeworkbook.unprotect or
activesheet.unprotect does not work.

Thanks.

SG

 

[Dave Peterson]

Seems like a good idea to me.

If you decide to do that, you may want to add a new post in this thread stating
what you did. It'll keep most people happy.

Thnaks. I am not sure how this discussion forum works but do you think if I
re-post it with a diiferent title , someone else might look at it.

Thanks.

 

[SG]

Thanks. Just want to mention here that since the discussion on this thread
has led to the cause of the problem and that relates to a differnet topic, I
am posting that under " ".
SG