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.

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.

SG wrote:
>
> 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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
> SG wrote:
>>
>> 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 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 wrote:
>
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >
> > SG wrote:
> >>
> >> 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

--

Dave Peterson

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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 wrote:
>>
>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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.
>> >
>> > SG wrote:
>> >>
>> >> 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
>
> --
>
> Dave Peterson

Whew!

I forgive you <vvbg>.

Jon Peltier wrote:
>
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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 wrote:
> >>
> >> 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
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >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.
> >> >
> >> > SG wrote:
> >> >>
> >> >> 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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" wrote:

> Whew!
>
> I forgive you <vvbg>.
>
> Jon Peltier wrote:
> >
> > 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
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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 wrote:
> > >>
> > >> 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
> > >> -------
> > >> Jon Peltier, Microsoft Excel MVP
> > >> Tutorials and Custom Solutions
> > >> Peltier Technical Services, Inc. - http://PeltierTech.com
> > >> _______
> > >>
> > >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> > >> news:(E-Mail Removed)...
> > >> >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.
> > >> >
> > >> > SG wrote:
> > >> >>
> > >> >> 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson
>

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

SG wrote:
>
> 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" wrote:
>
> > Whew!
> >
> > I forgive you <vvbg>.
> >
> > Jon Peltier wrote:
> > >
> > > 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
> > > -------
> > > Jon Peltier, Microsoft Excel MVP
> > > Tutorials and Custom Solutions
> > > Peltier Technical Services, Inc. - http://PeltierTech.com
> > > _______
> > >
> > > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > >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 wrote:
> > > >>
> > > >> 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
> > > >> -------
> > > >> Jon Peltier, Microsoft Excel MVP
> > > >> Tutorials and Custom Solutions
> > > >> Peltier Technical Services, Inc. - http://PeltierTech.com
> > > >> _______
> > > >>
> > > >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > >> news:(E-Mail Removed)...
> > > >> >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.
> > > >> >
> > > >> > SG wrote:
> > > >> >>
> > > >> >> 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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.

"Dave Peterson" wrote:

> Just so you know someone read your message...I have no idea. Sorry.
>
> SG wrote:
> >
> > 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" wrote:
> >
> > > Whew!
> > >
> > > I forgive you <vvbg>.
> > >
> > > Jon Peltier wrote:
> > > >
> > > > 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
> > > > -------
> > > > Jon Peltier, Microsoft Excel MVP
> > > > Tutorials and Custom Solutions
> > > > Peltier Technical Services, Inc. - http://PeltierTech.com
> > > > _______
> > > >
> > > > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > >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 wrote:
> > > > >>
> > > > >> 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
> > > > >> -------
> > > > >> Jon Peltier, Microsoft Excel MVP
> > > > >> Tutorials and Custom Solutions
> > > > >> Peltier Technical Services, Inc. - http://PeltierTech.com
> > > > >> _______
> > > > >>
> > > > >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > > >> news:(E-Mail Removed)...
> > > > >> >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.
> > > > >> >
> > > > >> > SG wrote:
> > > > >> >>
> > > > >> >> 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
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> 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.

SG wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > Just so you know someone read your message...I have no idea. Sorry.
> >
> > SG wrote:
> > >
> > > 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" wrote:
> > >
> > > > Whew!
> > > >
> > > > I forgive you <vvbg>.
> > > >
> > > > Jon Peltier wrote:
> > > > >
> > > > > 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
> > > > > -------
> > > > > Jon Peltier, Microsoft Excel MVP
> > > > > Tutorials and Custom Solutions
> > > > > Peltier Technical Services, Inc. - http://PeltierTech.com
> > > > > _______
> > > > >
> > > > > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > >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 wrote:
> > > > > >>
> > > > > >> 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
> > > > > >> -------
> > > > > >> Jon Peltier, Microsoft Excel MVP
> > > > > >> Tutorials and Custom Solutions
> > > > > >> Peltier Technical Services, Inc. - http://PeltierTech.com
> > > > > >> _______
> > > > > >>
> > > > > >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> > > > > >> news:(E-Mail Removed)...
> > > > > >> >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.
> > > > > >> >
> > > > > >> > SG wrote:
> > > > > >> >>
> > > > > >> >> 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
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson