# Excel 2003 / Excel 2010 Compatibility Issue ??



## monirg (Oct 13, 2011)

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.


----------



## Derek Brown (Oct 13, 2011)

Two thoughts:
1. Are you 'converting' the workbooks from 2003 format to 2007/2010 format? (File | Info | Convert)
2. Are you opening the files from a Trusted Location? (File | Options | Trust Center .....)


----------



## monirg (Oct 14, 2011)

Hi *Derek*;

Converting and Macro Settings made no difference!

*1)* When I open the XL 2003 file in XL 2010, and under:
*Developer::Macro Security* –> *Trust Centre::Macro Settings*
I have the following two boxes *checked*:
*O* Disable all macros with notification
*O* Trust access to the VBA project object model

*2)* And under:
*Trust Centre::Message Bar*
I have the top box *checked*:
*O* Show the Message Bar in all ...
and the bottom box *unchecked*: (I don’t know what it does!)
O Enable Trust Centre logging

*3)* Do you have the same settings on your system ??

*4)* I’ve tried different combinations for Macro Settings with no luck.

*5)* Incidentally, I’ve never seen the *Message Bar* displayed even once so far!
I thought it would be a good idea to have the message “Enable macros” “Disable macros” displayed when you open an excel file in XL 2010 (like in the good old days: XL 2003, Macro Security set to Medium).
Even with the above option "Trust access to the VBA project object model" *unchecked*. 

*6)* MS Excel Technical Support Team Manager has just replied:


> ... will forward this information to the concerned team at MS (to investigate) and will let you know.


Regards.


----------



## xenou (Oct 14, 2011)

When you protect worksheets with these charts on them, have you specified that you will allow users to select charts?

ActiveSheet.Protect _DrawingObjects:=False_


----------



## monirg (Oct 14, 2011)

Hi *xenou;*

*1)* Hope I understood and tried your suggestion correctly:
-> opened the w/s_password_protected XL 2003 w/b in XL 2010.
-> in the macro code, I inserted the statement:

```
ActiveSheet.Protect DrawingObjects:=False
```
before: 

```
ActiveSheet.ChartObjects("Chart 1").Activate
```
-> ran the macro
-> a MsgBox “Unprotect Sheet” appeared asking for the Password.
-> entered the password and pressed OK
-> the w/s protection was removed and everything worked fine
*Same* as manually unprotecting the w/s (my earlier suggestion)

*2)* Still in XL 2010, Cancel the “Unprotect Sheet” MsgBox (i.e.; keep the w/s protected), run the macro, and the Run-time error ‘1004’ re-appears with the following statement highlighted:

```
ActiveSheet.ChartObjects("Chart 1").Activate
```
Same as reported earlier.

Regards.


----------



## xenou (Oct 14, 2011)

> Same as manually unprotecting the w/s (my earlier suggestion)
> 
> 2) Still in XL 2010, Cancel the “Unprotect Sheet” MsgBox (i.e.; keep the w/s protected), run the macro, and the Run-time error ‘1004’ re-appears with the following statement highlighted:
> Code:



No.

You want to protect the sheet with drawing objects allowed to be edited.  I.e., you cannot activate a chart on a protected sheet (maybe you could before but now you can't).  Another possibility would be to try UserInterfaceOnly = True, though I've had mixed results with that and I don't consider it to be reliable).

The point is to set the protection more precisely.  Not just "protect" but protect with the exact options you want.

Edit:
You can also, btw, just unprotect the sheet, do your code thing, and re-protect it.  This is pretty much standard procedure in most cases where you need to do work on a protected sheet (unless you are employing the UserInterfaceOnly option, which I don't quite trust).


----------



## xenou (Oct 14, 2011)

I'm not a chart wiz with VBA but maybe it is not necessary to _activate _the chart.  Though my guess is that you would still need it to be in an editable state as far as protection options are concerned (not sure - haven't tried it).


----------



## monirg (Oct 14, 2011)

Hi *xenou;*

*1)* I meant to say: “Same *results*” in the quote you included. Sorry!
*2)* I did try earlier *UserInterfaceOnly = True*; and it didn’t solve the problem!
*3)* As far as your suggestion:


> just unprotect the sheet, do your code thing, and re-protect it


which was the logical thing to try: 


> *Monir* wrote in the *OP*:
> *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)* I suspect the message:


> Run-time error ‘1004’
> Application-defined or object-defined error


with reference to the code statement:
*ActiveSheet.ChartObjects("Chart 1").Activate*
may not necessarily (at least in this case) be a true VBA-Library-Generated Notification to exactly where the the problem is detected by XL 2010 (and not by XL 2003).
Just a thought! 
*5)* You questioned:


> maybe it is not necessary to _activate _the chart.


I’ve looked at a number of charts (incl. the chart in question), and it is necessary to activate the chart in the relevant VBA code.

Regards.


----------



## xenou (Oct 14, 2011)

I can't find a .minimumscale attribute for category axis in XL2010.  Have you noticed that?

I also don't, by the way, think that 2003 code is supposed to be 100% executable in 2010.  The chart object model, in particular, underwent significant changes.  Can you support your claim about Microsoft's claim ("_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_")?


----------



## monirg (Oct 14, 2011)

Hi *xenou;*
Thank you for your thoughtful reply.
*1)* You wrote:


> I can't find a* .minimumscale* attribute for category axis in XL 2010. Have you noticed that?


Very interesting! I haven’t noticed that, but will look at it after the game!

*2)* You wrote:


> I also don't, by the way, think that 2003 code is supposed to be 100% executable in 2010. The chart object model, in particular, underwent significant changes.


That’s true, but don’t think it could be blamed for the error, and here’s why

*3)* Realizing one needs only a very minor deviation from the VBA code conformity to get an error, one might reasonably argue that neither *1)* or *2)* above is/could be the culprit, since by manually removing the w/s password protection from the XL 2003 w/b and doing nothing else, the w/b works fine and as desired in XL 2010.

Regards.


----------



## monirg (Oct 13, 2011)

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 (Oct 14, 2011)

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 (Oct 14, 2011)

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 (Oct 14, 2011)

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 (Oct 14, 2011)

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 (Oct 15, 2011)

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


----------



## monirg (Oct 15, 2011)

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 (Oct 15, 2011)

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 (Oct 15, 2011)

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 (Oct 15, 2011)

> 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:
> ...



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 (Oct 15, 2011)

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.


----------



## monirg (Oct 13, 2011)

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 (Oct 15, 2011)

Change:

```
Set chrt = ws.ChartObjects("Chart 1")
```
To:

```
Set chrt = ws.ChartObjects("Chart 1").Chart
```

My bad - I don't code for charts a lot but I should have remembered this.  As an added plus, when you start writing your code using the chrt variable, intellisense will kick in and you'll start to see the properties and methods for charts.  You could also dim an axis variable too:


```
Dim ws As Worksheet
Dim chrt As Chart
Dim x As Axis

Set ws = ActiveSheet
Set chrt = ws.ChartObjects("Chart 1").Chart
Set x = chrt.Axes(xlCategory)
```

This is a helps when learning about objects on the fly - just to get an idea of what their properties are and what you can work with.


----------



## monirg (Oct 15, 2011)

Hi *xenou*;

Getting closer, but no cigar yet!

Now the run-time error is down couple more statements in the same trouble section of the code and within the *IF* block.
This is encouraging in a sense ... It is moving down!


> Run-time error ‘-2147467259 (80004005)’:
> Method ‘MinimumScale’ of object ‘Axis’ failed


and the following statement is highlighted:

```
[B].MinimumScale = 0.7[/B]
```
Regards.


----------



## xenou (Oct 15, 2011)

yes, well.  At least we are now getting the same error in the same place.  I can't understand this one.  When I explore the chart axis members, I don't find that a category axis has a minimum scale that can be set.  So I don't know how this works.

Maybe this *is* something that has changed in the new chart object model.  What kind of data is in this chart?  What's on this axis?  [Note: At this point it may be useful to try to record this change as you make it "by hand" and see how the macro recorder treats it - but, as I said, I couldn't find this either in code or in the XL2010 Chart options on screen].


----------



## monirg (Oct 15, 2011)

Hi *xenou*;


> What kind of data is in this chart? What's on this axis?


It is an XY Chart, multiple series, numerical data, linear scale, with the min and max X-Axis values as specified in the code.
The rest is automatic.

I'm searching the XL 2010 VBA Help again on *Axes (xlCategory)*. I haven't found yet the method/property that returns or sets the min or max of the category axis in a chart. I'm sure it is there somewhere.
Even pressing *F1* on *MinimumScale* or *MaximumScale* in the code doesn't return any helpful info.

Regards.


----------



## NdNoviceHlp (Oct 16, 2011)

This works for me in every version and MS OS. HTH. Dave

```
With Sheets("Sheet1").ChartObjects(1).Chart.Axes(xlCategory)
.MinimumScale = Format(Sheets("Sheet1").Cells(1, 1).Value, "###0")
.MaximumScale = Format(Sheets("Sheet1").Cells(Lastrow, 1).Value + 1, "###0")
End With
```


----------



## monirg (Oct 16, 2011)

Hi *Dave*;

Thank you for your suggestion.
I’m afraid it also produced the same run-time error reported earlier in post # *22*:
*“Method ‘MinimumScale’ of object ‘Axis’ failed” *

For whatever reason, it appears that XL 2010 doesn’t like setting the *xlCategory* by the *Axis.MinimumScale* property.
I suspect the property works fine with the value axis as described in "Excel 2010 Help".  

Regards.


----------



## NdNoviceHlp (Oct 16, 2011)

The code works for XL2010 for me? Dave


----------



## xenou (Oct 16, 2011)

Okay, going for me too when I use an XY chart (I was able to see these options in the axis dialogue once I changed the chart type to XY, and then record a macro too).


----------



## monirg (Oct 16, 2011)

Hello;

If it is not too much trouble, could you please replace your XY-chart recorded macro code with either of the following code segments to see if either runs error free in your XL 2010 ??
It would be greatly appreciated!

*1)* *Code A*:
--> In Monir XL 2003 (Win XP 32-bit):
..... *Works perfectly*
--> In Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit):


> *Run-time error ‘1104’:*
> *Application-defined or object-defined error*
> ... with ref to the *highlighted* statement in the code




```
[B][SIZE=3]ActiveSheet.ChartObjects("Chart 1").Activate[/SIZE][/B]
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
```
*2)* *Code B*:
--> In Monir XL 2010 (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit):


> *Run-time error ‘-2147467259 (80004005)’:*
> *Method ‘MinimumScale’ of object ‘Axis’ failed*
> ... with ref to the *highlighted* statement in the code




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


----------



## xenou (Oct 16, 2011)

yes. Works.  I think you'll have to give some sample data that is being plotted.  I think it has to to with the arrangement of the chart.  Sorry I can't understand it better - I've just spent 10 minutes trying to work out some x-y chart that seems to fit what you are doing and realized I'm not getting anywhere (though I could run your code on my stupid chart that probably is nothing like what you have).  Charts can be tricky beasts.


----------



## monirg (Oct 13, 2011)

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.


----------



## monirg (Oct 16, 2011)

Hi *xenou*;


> yes. Works.


You mean both *Code A* and *Code B* work with no run-time error in your XL 2010 ??



> I think you'll have to give some sample data that is being plotted


Agree. I’m working almost non-stop on a sample abbreviated file!

Thank you.


----------



## xenou (Oct 16, 2011)

Here's my test of concept.  
It just shows that it works (nothing fancy).  

Note that I found I did have problems when I protected the chart and ran the code, so I believe you have to unprotect, run the changes, then protect.  In fact, this gets me the same error, except nothing about methods failing (just "unspecified error").  That could be a coincidence.

Sample file: http://northernocean.net/etc/mrexcel/20111016_monirg.zip
sha256 checksum (zip file): 481abc9c0aafa1ee79b965c6e54eeecd1f6071dc4e281f8b9dfd643ae2391eca


----------



## monirg (Oct 17, 2011)

Hi *xenou*;

Thanks again for your help, patience and generous time!

*1)* Macro “*Sheet1.Foo*” in your sample w/b *.xlsm (with unprotected w/s, no activation of chart, no selection of chart area, and no selection of axis category) runs fine in my XL 2010.
Similar to your observation: protect the w/s with a password, run the macro, and cancel the “Unprotect sheet” message, and I get “MS VBA” error 400. Again as you noted, nothing about methods failing!

*2)* I’m re-examining your XL 2010 recorded “macro1” (which has a number of redundant statements as one would expect in a recorded macro!) and compare it with my_ *Code A*_ (post # 29, reproduced below), to see if there’s a particular sequencing in XL 2010 chart code that wasn’t in XL 2003.

*3)* *Code A*:
--> Monir XL *2003* (32-bit, Win XP 32-bit), with w/s password *protection*:
..... *Works perfectly*
--> Monir XL *2010* (Full version, Home & Student, 32-bit, Win 7 Pro 64-bit), w/s password *protected*:


> *Run-time error ‘1104’:*
> *Application-defined or object-defined error*
> ..... with ref to the highlighted statement in the code below


--> Monir XL *2010 *(Full version, Home & Student, 32-bit, Win 7 Pro 64-bit), w/s *unprotected*:
..... *Works perfectly*

```
[SIZE=3][B]ActiveSheet.ChartObjects("Chart 1").Activate[/B][/SIZE]
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
```
Regards.


----------



## xenou (Oct 17, 2011)

Oops.  I had not intended to have any recorded code in my sample workbook.  Remove or ignore Module 1.  The code I wrote is in Sheet1.  I would not advise (for now) any activation of charts or selection of chart elements.

This is what we are aiming at:


> 1) Macro “Sheet1.Foo” in your sample w/b *.xlsm (with unprotected w/s, no activation of chart, no selection of chart area, and no selection of axis category) runs fine in my XL 2010.


To me that sounds like the problem is solved now.  You only need to unprotect, make your chart changes as needed, and protect the workbook.  As far as working or not working in XL2003, who cares - it's an xlsm now running in 2010.  Is this going in circles now?  What I mean is, I would not try to use the XL2003 code if you are now using XL2010.  Write it so it works in XL2010.

ξ


----------



## monirg (Oct 17, 2011)

Hi *xenou*;



> Remove or ignore Module 1


Done!


> Is this going in circles now?


It seems so at some level!

The whole exercise can be summarized in one or two paragraphs.

*1)* I have 50+ XL 2003 workbooks, each runs perfectly in XL 2003, and I need to transfer them to the recently acquired XL 2010 on a new laptop (4 times faster!).
Each w/b has at least one password protected w/s to preserve the sheet formatting and preventing unintentional changes.
None of those workbooks would run error-free *As Is* in my XL 2010, producing a run-time error with ref to the XL 2003 chart code.

*2)* I picked up one of those workbooks as a case study and tried to figure out the cause of the problem.
By pure accident, I manually removed the w/s protection and tried again. The workbook surprisingly worked fine in XL 2010 with no errors. I tried another w/b after unprotecting its w/s, and it also worked perfectly and as desired.

*a. Does* this mean the chart code (*Code A*, post # *33*) is compatible, and perhaps it’s just a matter of carefully re-checking the “XL 2010 Options” ?? ... Perhaps!
*b. Could* it be that the XL 2003 VBA is not 100% compatible with XL 2010 VBA (particularly when it comes to chart code; as you’ve suggested earlier) and I need to dig deeper instead of learning XL 2010 on the fly ?? (Needless to say, I’ve learned a lot from the discussion!)
*c.* *Could* the issue be something particular to my situation (as you’ve also suggested earlier) ?? 

*3)* The simplest/easiest workaround solution for me and for now, though inconvenient, is as follows:
*i.* open any of the 50+ XL 2003 workbooks in XL 2010
*ii..* manually unprotect the w/s
*iii.* change nothing in the code, and run the macro
*iv.* if the file is “Read only” and want to save the changes:
--> manually protect the w/s
--> use “Save As” with a new name and the *xlsm* format
*v.* otherwise:
--> exit without saving the file
--> it would be still protected and in the old *xls* (compatible) format, so I’d know which is which!

Regards.


----------



## xenou (Oct 17, 2011)

> i. open any of the 50+ XL 2003 workbooks in XL 2010
> ii.. manually unprotect the w/s
> iii. change nothing in the code, and run the macro



I would not consider the above easiest (necessarily) - certainly not if involving manually unprotecting anything in 50+ workbooks (i.e., why not add a line to the code to unprotect the chart while you edit it's elements in your routine?).  But only you can decide what's best in the overall situation.  You've got many variables at play here so it's not a simple thing to evaluate.

If you like, you can try to convince me why you need sheet protection.  My personal philosophy is to live without it - so how's that for easy?  I find I don't need it about 98% of the time - and never when it gets in the way.

ξ


----------



## monirg (Oct 17, 2011)

Hi *xenou*;


> *xenou* wrote:
> I would not consider the above easiest (necessarily). But only you can decide what's best in the overall situation.


Believe me, it’s the easiest workaround solution for me by far!
The logic in each chart code in each XL 2003 w/b is different, and having a one-size-fits-all code fix wouldn’t work for me.
More critically, my philosophy in writing codes is rather simple. I write the code, extensively test the procedure under different scenarios, and then move on.
I don’t claim any of my codes to be the most efficient, or you as an excel expert would design them the same way!



> *xenou* wrote:
> If you like, you can try to convince me why you need sheet protection.


Well, that’s easy, and may not require too much convincing!
*In XL 2010:* Can you block cells and hide formulas on the worksheet without protecting the w/s ??
If your answer is in the affirmative, then I’ll never use w/s protection again, period!

Regards.


----------



## xenou (Oct 18, 2011)

> In XL 2010: Can you block cells and hide formulas on the worksheet without protecting the w/s ??
> If your answer is in the affirmative, then I’ll never use w/s protection again, period!



Yes.  I don't look at them!


----------



## monirg (Oct 18, 2011)

Hi *xenou*;


> *Monir* wrote*:*
> In XL 2010: Can you (effectively) block cells and hide formulas on the worksheet without protecting the w/s ??
> 
> *xenou* wrote*:*
> Yes. I don't look at them!


OK. So by not looking at them you can prevent later unintentional changes!
Not sure, but wouldn’t argue.

Regards.
*Monir*


----------



## xenou (Oct 18, 2011)

Yes.  To be more specific, there are quite a lot of ways you can protect your data.  Sorry to be the bearer of bad news but you have probably chosen the weakest one of them all - password protecting a worksheet.  I wouldn't mind hearing more about why you have chosen this method and how you think it protects your data.  Generally speaking, since protection is weak anyway, for my part I don't bog down my workbooks with such things - I use lightweight methods for data I merely want to keep away from user's eyes, and for data that really must be kept safe I use better strategies than relying on Excel passwords.


----------



## monirg (Oct 13, 2011)

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.


----------



## monirg (Oct 18, 2011)

Hi *xenou*;


> *xenou* wrote*:*
> there are quite a lot of ways you can protect your data. you have probably chosen the weakest one of them all - password protecting a worksheet.


That is absolutely fascinating and very interesting to explore! Not just for me but probably for many excel users!



> *xenou* wrote*:*
> I wouldn't mind hearing more about why you have chosen this method (w/s password protection)


Well, primarily to prevent me later from unintentionally changing blocked cells (data and/or formulas), and in some instances, I simply don’t want to reveal critical formulas.
I know for fact that w/s password protection doesn’t necessarily provide a high level protection, but it provides some nevertheless.
Let me put it this way. It's a habit, and it is hard to break old habits! 

I feel that we’re drifting slowly but surely from the topic here, and I wonder if I should start a new Thread in *Excel Questions Forum*, specifically addressing the data protection issue.
A Thread with a Title like:
*w/s Protection: Are There Ways of Protecting Data in XL 2010 Other Than with a Password ??*
It would be a hot subject, I think, if it hasn’t already been discussed at length.

Regards.


----------



## xenou (Oct 18, 2011)

I can understand the reasoning, sort of (I do the same in one case where I think I might update the workbook in the wrong place because of a hack that would tempt me if I wasn't thinking).  But I generally find worksheet protection and workbook protection (with passwords) more trouble than its worth.  I might do this in one or two workbooks, but not 50 of them.  Mostly because they make writing vba more troublesome, and also I don't like having to constantly protect/unprotect sheets when I'm using them.  I previously used read-only protection a fair bit, with workbooks that I only updated once a month. I do actually protect charts, but just so that mouseclicks won't move them around or show chart handles.

My main strategies are: Keep backups of all important workbooks.  Use clearly defined structures so it's obvious where changes are appropriate or not appropriate.  Separate input data from process data and output data.  Using input cells and input data ranges.  Never leave confidential information in any workbook going to anyone who should not have it (no matter how safely I think I have protected or hidden it).  Use operating system permissions to protect files rather than passwords.  I create check figures and validations to confirm results are correct and alert me when expected conditions have been violated.  Aside from that, if I just want to make my workbooks modestly protected, I use invisible formatting, very hidden sheets, or send out washed copies that remove the process data and only retain the result data.  Most of my "public" workbooks are copies of the real things that stay safe and sound.  I am fortunate that I don't have to use any shared workbooks and can generally keep track of my own work in Excel, and also I can employ some Access tools and data import/export strategies so that I can work with others' data without giving them access to my workbooks.  (I guess that works both ways, I've had one boss that was terrified I would "do something" to his cash flow worksheet, which was a huge mass of manually typed-in data. Actually, I would surely have turned it upside down if I had a chance - so he basically wouldn't let me near it which was fine by me!)  In fact, most of my workbooks have some default "security through obscurity" built in - which is that most people can't figure them out (meaning, they can't figure out where to type or paste the numbers in, not realizing there are no numbers to type or paste in and I'm pulling the data in automatically and running it all by formulas).

I consider that many Excel users can't unhide a sheet or find white-colored text, while then the savvy user won't be phased even by passwords or very hidden sheets.  So I aim to turn aside the casual prying eyes only, which works for non-critical purposes, and for critical purposes I assume the worst - that I have to assume my users can crack passwords and find hidden sheets and so on.


----------



## monirg (Oct 20, 2011)

Hi *xenou*;

Thank you kindly for sharing your strategies. Very helpful.

Regards.


----------



## xenou (Oct 20, 2011)

I should add that I am *not* up to speed on the latest changes in password security with the newest versions of Excel - so I may be out of date to some extent so far as that goes.

ξ


----------

