 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Excel 2007 VBA
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 > [quoted text clipped - 4 lines] > > What could possibly be wrong with that? 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. 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 "teepee" <tee...@noemail.com> wrote... >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? 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? > For the heck of it, run the following with that worksheet active in > XL2007. [quoted text clipped - 10 lines] > > Didn't seem to do anything at all. Where would it write chart 542? 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  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 >I found the problem. The sheet protection option that allows for editing > objects doesn't work in 2007 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 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 > 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? 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 Teepee How about the UserForm_Deactivate() event? Private Sub UserForm_Deactivate() ActiveSheet.Protect End Sub SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 > 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. [quoted text clipped - 8 lines] > > TP ta good idea Teepee How about the UserForm_Deactivate() event? Private Sub UserForm_Deactivate() ActiveSheet.Protect End Sub SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 > [quoted text clipped - 10 lines] > > 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? Correct Thinking about it you should reference the sheet explicitly as it may ne be active Worksheets("Sheet1").Protect SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 > [quoted text clipped - 6 lines] > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.