Excel 2007 VBA

  • Thread starter Thread starter teepee
  • Start date Start date
T

teepee

Hello

I try to run a VBA code that woks fine in Excel 2003 and it crashes in 2007.
The offending line is

ActiveSheet.ChartObjects("Chart 542").Activate

What could possibly be wrong with that?
 
Hello

I try to run a VBA code that woks fine in Excel 2003 and it crashes in 2007.
The offending line is

ActiveSheet.ChartObjects("Chart 542").Activate

What could possibly be wrong with that?
Click to expand...

I apologize if I'm asking the obvious question, but does Chart542
exist in the activesheet? When you tested it in 2003, did you test it
on the same spreadsheet that you tested it on in 2007? What error do
you get when it crashes?
 
I apologize if I'm asking the obvious question, but does Chart542
exist in the activesheet? When you tested it in 2003, did you test it
on the same spreadsheet that you tested it on in 2007? What error do
you get when it crashes?

Indeed it does exist (if I record a macro and select the chart under 2007 it
generates exactly the same line) and the whole macro runs fine on the same
file under 2003. On 2007 it gives a run tim error 1004. Application or
object defined error.

I should say that other macros on this sheet also malfunction under 2007
that work fine under 2003. But I'm taking them one at a time.
 
teepee said:
I try to run a VBA code that woks fine in Excel 2003 and it crashes in
2007. The offending line is

ActiveSheet.ChartObjects("Chart 542").Activate

What could possibly be wrong with that?
Click to expand...

For the heck of it, run the following with that worksheet active in
XL2007.

Sub foo()
Dim x As Object
For Each x In ActiveSheet.ChartObjects
Debug.Print x.Name
Next x
End Sub

See if that throws errors or writes Chart 542 in the VBE's immediate
window.
 
For the heck of it, run the following with that worksheet active in
XL2007.

Sub foo()
Dim x As Object
For Each x In ActiveSheet.ChartObjects
Debug.Print x.Name
Next x
End Sub

See if that throws errors or writes Chart 542 in the VBE's immediate
window.

Didn't seem to do anything at all. Where would it write chart 542?
 
I recorded the following in 2007

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveSheet.ChartObjects("Chart 542").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 541").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 543").Activate
ActiveChart.PlotArea.Select
Selection.Left = 3.75
Selection.Top = 2.75
Range("E22").Select
End SubSub Macro1()

And when I ran it it still crashed on the first line
 
For the heck of it, run the following with that worksheet active in
XL2007.

Sub foo()
Dim x As Object
For Each x In ActiveSheet.ChartObjects
Debug.Print x.Name
Next x
End Sub

See if that throws errors or writes Chart 542 in the VBE's immediate
window.

Didn't seem to do anything at all. Where would it write chart 542?
Click to expand...

When you are in the visual basic editor, enable the immediate window
(View->Immediate window, or Ctrl-G) and then run the macro Harlan
suggested. It SHOULD print a list of the chart names to that window.
Check to see if Chart542 is one of the names printed. If it really
doesn't print anything (or prints a bunch of blank lines), then try
added the line
debug.print activesheet.chartobjects.count
It will print the number of charts in the activesheet to the immediate
window. If it prints the number 0, then we have an interesting
dilemma.
I hope that helps.
 
It will print the number of charts in the activesheet to the immediate
window. If it prints the number 0, then we have an interesting
dilemma.

Thanks. It did. It printed

Chart 541
Chart 542
Chart 543

Which are indeed the three chart objects in the sheet
 
Weirder still, if I create a sheet within 2007 it has no problem with the
commands but if it's created within 2003 it can't cope.
 
I found the problem. The sheet protection option that allows for editing
objects doesn't work in 2007
 
Teepee

Charts and shapes in Office are now a shared object, not specific to the
Excel application, therefore the protection through VBA is broken as it is
no longer a DrawingObject. It still appears to work through the interface

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
"Nick Hodge" wrote

Charts and shapes in Office are now a shared object, not specific to the
Excel application, therefore the protection through VBA is broken as it is
no longer a DrawingObject. It still appears to work through the interface

Thanks Nick. Does that mean I can't use macros on my charts without
unprotecting my sheets?
 
Teepee

I would say, from my testing yes. To be honest, I've always done that
unprotect, change, protect as these were not available pre XP. Expect it to
make a comeback though as the object model in VBA gets updated to the new
objects

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk
 
"Nick Hodge" wrote

I would say, from my testing yes. To be honest, I've always done that
unprotect, change, protect as these were not available pre XP.

Thanks Nick. I think I can workaround. I put ActiveSheet.Unprotect at the
top of the userform that controls the VBA. I just need to find a way to put
ActiveSheet.Protect in automatically when the userform exits. If you have
any ideas....

Thanks for your advise

TP
 
"Nick Hodge" wrote

How about the UserForm_Deactivate() event?

Private Sub UserForm_Deactivate()
ActiveSheet.Protect
End Sub

And my final dumb question - why does this subcommand not work. Presumably
it goes inside the userform?
 
Thanks for all your help Nick.

I've got it more or less up and running in 2007 now, albiet with some of the
functionality missing - but good enough until Microsoft gets round to
sorting out the compatability issues - if they ever do.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 VBA problem 7
Runtime Error 1004 1
Missing code in macro 2
Axes(xlCategory) for a chart-sheet in 2007 2
VBA: 2007 ==> 2010, How Badly Broken? 7
Dynamic charts with named range Windows 8.1 3
excel 2007 errors on chart changes 1
Excel 2007 chart line width + axis labelling with VBA 6

Back
Top