Excel 2003 / Excel 2010 Compatibility Issue ??

[monirg]

Hello;

My earlier Thread in Excel Questions Forum:
VBA: Opening XL 2003 w/b in XL 2010 Produces Error ‘1004’ ??
http://www.mrexcel.com/forum/showthread.php?t=583852

I’d like to regard the problem as a compatibility issue rather than an excel question; particularly for those who might be interested in the subject or have encountered a similar problem.

Here’s in a nutshell a description of the problem and my earlier solution.

1) I need to transfer many of my XL 2003 workbooks (developed and run perfectly on different computers, XL 2003, Win XP 32-bit) to my new laptop (Win 7 Pro 64-bit, Office 2010 full version, 32-bit).

2) Any of those XL 2003 workbooks (with w/s password protection, macros, w/s events) produces in XL 2010 the error:
“Run-time error ‘1004”

3) In XL 2010, and been annoyed with the run-time error, I tried a w/b Open() event to unprotect the w/s in the transferred XL 2003 w/b, and a w/b BeforeClose() event to protect the w/s before exiting. Also tried calling a Sub to unprotect the w/s directly before doing anything. Nothing worked.
The Run-time error ‘1004’ persisted and appeared at different locations / different routines depending on the code changes.

4) Even unprotecting the w/s in the XL 2003 w/b before transferring the file to the new m/c running XL 2010 and then protecting the w/s in the XL 2010 environment before save/open didn’t work!
My earlier “inconvenient” solution (post # 6 in the above link)

to manually remove the w/s password protection (either before or after the transfer), and keep the w/s unprotected

remains the only solution (so far!) that’s working for me in XL 2010.

5) Since MS claims that a working XL 2003 w/b would run successfully AS IS in XL 2010 (i.e.; no VBA code modifications, no added events, no w/s attribute changes), I wonder if the problem should be looked at by MS Excel Development Team to possibly identify the XL 2010 bug and fix the problem once and for all.
(Will post here the MS solution, if any!)

Any comments ?? Is communicating with MS Excel Team worth the time and effort ??

Regards.

 

[xenou]

This does work for me in XL2010 (With an .xls file):

ActiveSheet.Protect DrawingObjects:=False
ActiveSheet.ChartObjects("Chart 1").Activate

I'm not sure why you don't just allow edits to the chart, make your changes, and then if desired, protect the sheet with chart edits disallowed. I can't actually run your code because I can't access a minimum scale on a value axis, but I guess that's another issue.

 

[xenou]

This also works for me (using something else beside minimum scale to test with):

Sub Foo()
Dim x As Axis
Dim cht As Chart
Dim ws As Worksheet

    Set ws = ActiveSheet
    ws.Protect DrawingObjects:=True, UserInterfaceOnly:=False
    Set chrt = ws.ChartObjects("Chart 1").Chart
    Set x = chrt.Axes(xlValue)
    x.HasMajorGridlines = False

End Sub

I don't think it's best to activate the chart anyway - use a chart object, without activation. As far as I'm concerned, there's no reason to activate a chart in code on a protected worksheet so it's moot.

 

[xenou]

This also works:

Sub Foo()
Dim chrt As Chart
Dim x As Axis

Dim cht As Chart
Dim ws As Worksheet

Set ws = ActiveSheet
ws.Protect DrawingObjects:=True, UserInterfaceOnly:=True
Set chrt = ws.ChartObjects("Chart 1").Chart
Set x = chrt.Axes(xlValue)
x.HasMajorGridlines = False

End Sub

It seems that you can edit the chart with code even if its protected (actually, whether UIOnly is false or true!). I think it's something particular to your situation that is the issue - you might need to provide more exact details or a sample file in order to replicate the error (I can't create the problem).

 

[monirg]

Hi xenou;

A) You asked:

why you don't just allow edits to the chart, make your changes, and then if desired, protect the sheet

I did try that as reported in my reply # 8; Item 3.

B) The results based on your latest suggestions (posts # 11, 12, 13) are exactly as I reported earlier (my reply # 5):
I opened the w/s_password_protected XL 2003 w/b in XL 2010, and in the macro code (standard module) I inserted:
ActiveSheet.Protect DrawingObjects:=False
or
ActiveSheet.Protect DrawingObjects:=True, UserInterfaceOnly:=False
or
ActiveSheet.Protect DrawingObjects:=True, UserInterfaceOnly:=True

before the statement:
ActiveSheet.ChartObjects("Chart 1").Activate

C) I ran the macro for each of the above 3 changes.
A MsgBox “Unprotect Sheet” appeared asking for the Password.
I entered the password and pressed OK.
The w/s protection was removed and everything worked fine
Same result as manually unprotecting the w/s (my earlier suggestion)

D) You wrote:

I think it's something particular to your situation that is the issue - you might need to provide more exact details or a sample file.

The project was partially completed in 2009 and the work is CLASSIFIED.
Anticipating such request, I have been working in the last few days on a sample abbreviated file from the original:
removing few macros, deleting couple of work sheets, changing texts, renaming modules, etc., while still replicating the error.
The end product so far is horrible!
Remote debugging is by no means easy, and will keep working on the sample file. Hopefully will have it ready sooner than later.
Please allow me some time since the odds aren't in my favour!

Regards.

 

[xenou]

So if you can make the changes what's the problem?

 

[monirg]

Hi xenou;

if you can make the changes what's the problem?

Well, the problem is trying to run the XL 2003 workbooks in XL 2010 without making changes!
I haven’t been able to accomplish that.

So far, the only feasible (and inconvenient) solution to error ‘1004’ is to remove the w/s password protection (manually or via code) after opening the file in XL 2010, then run the macros, and protect the w/s before saving the file in the XL 2010 format.

Even so, after opening the *.xlsm w/b, one still needs to unprotect the w/s before running the macros, otherwise the Run-time error ‘1004’: “Application-defined or object-defined error” will pop up again with ref to the macro code statement:
ActiveSheet.ChartObjects("Chart 1").Activate

All these troubles are non existent when running the same *.xls files in XL 2003, and that’s the mystery!

Regards.

 

[xenou]

Have you tried running the code without activating the chart? It normally isn't necessary to activate charts. Otherwise, it's very hard to offer concrete suggestions without a sample of the actual code that will demonstrate the problem - as I've said, this error doesn't happen for me so it appears its not a global problem with Excel but something unique to your case. The most general solution in such a case is to unprotect the sheet, make your chart changes via your code, and re-protect the sheet.

Note: another idea - if you are converting the file to an actual xlsm file format, try converting the file with sheet protection off. Put it back on after you've converted the file.

 

[monirg]

Hi xenou;

1) You asked:

Have you tried running the code without activating the chart? It normally isn't necessary to activate charts.

Yes I have.
When I commented out the chart activation statement: (*.xls file, password protected w/s, in XL 2010)
ActiveSheet.ChartObjects("Chart 1").Activate
I got error ‘91’ instead of error ‘1004’:

Run-time error ‘91’:
Object variable or With block variable not set

and the statement next to the_commented_out statement was highlighted:
ActiveChart.ChartArea.Select

2) Interestingly enough, if I comment out that entire section of the code: (*.xls file, password protected w/s, in XL 2010)

'change chart XR scale depending on TIP or HUB region
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
   With ActiveChart.Axes(xlCategory)
     If Range("J41") = "TIP" Then
        .MinimumScale = 0.7
        .MaximumScale = 1.05
     Else
        .MinimumScale = 0.15
        .MaximumScale = 0.71
     End If
   End With

the macro runs fine with no errors (but of course produces the wrong display).

3) Could it be that the above code (or part of it) is permissible in XL 2003 but not in XL 2010 ??
Hard to imagine though, since leaving the above code in and unprotect the w/s, and everything work fine in XL 2010.

4) You wrote:

Note: another idea - if you are converting the file to an actual xlsm file format, try converting the file with sheet protection off. Put it back on after you've converted the file.

I also tried that! It didn’t solve the problem.

Regards.

 

[xenou]

When I commented out the chart activation statement: (*.xls file, password protected w/s, in XL 2010)
ActiveSheet.ChartObjects("Chart 1").Activate
I got error ‘91’ instead of error ‘1004’:
Quote:
Run-time error ‘91’:
Object variable or With block variable not set
and the statement next to the_commented_out statement was highlighted:
ActiveChart.ChartArea.Select

This one's an expected error that we can remedy (hopefully):

Dim ws As Worksheet
[B]Dim chrt As Chart[/B]
    Set ws = ActiveSheet
[B]    set chrt = ws.ChartObjects("Chart 1")[/B]
    With chrt.Axes(xlCategory)
        If ws.Range("J41") = "TIP" Then
            .MinimumScale = 0.7
            .MaximumScale = 1.05
         Else
            .MinimumScale = 0.15
            .MaximumScale = 0.71
         End If
    End With

In the above code we don't activate or select anything. As a general rule, it is highly recommended to write code this way - it will run faster, with less screen flicker, and the use of object references will make it easier to maintain.

[Note that the above is not tested and hand written just now so apologies if there's any syntax errors].

 

[monirg]

Hi xenou;

There’s a mismatch data type in:
Set chrt = ws.ChartObjects("Chart 1")
It produces the error:

Run-time error ‘13’:
Type mismatch

Don’t know why, since the data type of “chrt” and “ws” are declared correctly.

I checked the chart name for the 100th time! It is the correct name.
I also checked to see if either (or both) of the above objects names is now a reserved keyword in XL 2010 VBA and can’t be assigned to a variant. They are not.

It is interesting that the above run-time error pops up whether the w/s is password protected or not.
This is something new!

Regards.