Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 he 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" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 he 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" wrote in message ... 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 he 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" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 he 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" wrote in message ... 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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 VBA - Run-time error 1004
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 "Dave Peterson" wrote: 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 he 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Error Code 1004 when exporting from VB app to Excel 2007 | Excel Discussion (Misc queries) | |||
Run-time error '1004' VBA Excel 2003 | Excel Programming | |||
EXCEL VBA - Run-time Error 1004 | Excel Programming | |||
EXCEL VBA - Run-time Error 1004 | Excel Programming |