Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

ActiveSheet.ChartObjects ("MyChart")-- error

HI,
ActiveChart.ChartArea.Select
    ActiveSheet.ChartObjects ("Chart 2")
   ActiveSheet.Shapes("Chart 2").ScaleHeight 1.28, msoFalse, msoScaleFromBottomRight
    ActiveSheet.Shapes("Chart 2").ScaleWidth 1.46, msoFalse, msoScaleFromBottomRight
    ActiveSheet.Shapes("Chart 2").ScaleWidth 1.29, msoFalse, msoScaleFromTopLeft
   ActiveSheet.Shapes("Chart 2").ScaleHeight 1.37, msoFalse, msoScaleFromTopLeft
generates an error
Unable to get chartobjects property for worksheet class
Can anybody help me on this.
Thankyou


Post your answer or comment

comments powered by Disqus
Hi All

I have craeted a chart in VBA, which sits in the middle of the page . I have attempted to move it to a new location with the following code

	VB:
	
ActiveChart.Parent.Name = "MyChart" 
 
ActiveSheet.ChartObjects("MyChart").IncrementLeft -237.75 
ActiveSheet.ChartObjects("MyChart").IncrementTop 32.25 
ActiveSheet.ChartObjects("MyChart").ScaleHeight 0.96, msoFalse, msoScaleFromTopLeft 
ActiveSheet.ChartObjects("MyChart").ScaleWidth 1.17, msoFalse, msoScaleFromTopLeft 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which I adapted from a recorded macro, but which fails in execution, error message indicates that the Object does not support the property or Method.

can someone see where Iam going wrong, please?

Regards

Robert

I ma trying to use vba to insert an xl linked chart in word. I have found examples of using thye pastespecial method with linked set to false which work. however, if i change linked to true the pastespecial fails.
Error is 5342 - Specified data type is unavailable
Help would be much appreciated
Alan
eg

	VB:
	
 OpenAndReadExcelWB() 
     
    Dim xlApp As Excel.Application 
    Dim xlWB As Excel.Workbook 
    Dim tString As String, r As Long 
     'Documents.Add ' create a new document
    Set xlApp = CreateObject("Excel.Application") 
    xlApp.Visible = True 
     
    Set xlWB = xlApp.Workbooks.Open("C:alan.xls") 
    ActiveSheet.ChartObjects("mychart").Copy 
    ActiveDocument.Tables(1).Cell(2, 2).Select 
    Selection.PasteSpecial Link:=True, Placement:=wdInLine, _ 
    DisplayAsIcon:=False, DataType:=wdPasteMetafilePicture 
     'Selection.PasteAndFormat (wdChartLinked)
     'Selection.Paste
     
    xlWB.Close False ' close the workbook without saving
    xlApp.Quit ' close the Excel application
    Set xlWB = Nothing 
    Set xlApp = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Yesterday duncanslam (Steve Duncan) posted in Excel.misc for a way to
hide charts that don't have data. Seemed doable to me, so I had a go
only to discover the nasty problems associated with returning the
address of a chart's data.
Andy Pope cleared most of that up for me, thanks again Andy, so I then
had a go and thought I figured out a way of hiding dataless embedded
charts...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cht As ChartObject
Dim strSeries As String
For Each Cht In ActiveSheet.ChartObjects
On Error GoTo BLANK_CHART
strSeries = Cht.Chart.SeriesCollection(1).Formula
Cht.Visible = True
GoTo ALL_CHARTS
BLANK_CHART: Cht.Visible = False
ALL_CHARTS: Next Cht
End Sub

I had three XY scatter charts on the sheet, all with their own separate
X and Y values, and when I deleted the data (either X, Y or both), the
affected chart became invisible.

Unfortunately, when I delete the data of a second chart it results in
the Run-time error 1004 Unable to get the Formula Property of the
Series Class.

If I click End on the error message dialog the second (dataless) chart
is invisible, so at least the code worked.

So it seems to me like the On Error GoTo BLANK_CHART line somehow fails
for the second dataless chart.

Is there anyway around this problem?

Ken Johnson

Hi All,

I have the below code which re-sizes all charts on a sheet with one's chart's size. I want to actually give the user a input option where user can enter the width and height in a inputbox and then macro will resize all charts on the sheet with the same size.

Sub ResizeAndArrangeChartObjects()
W = ActiveSheet.ChartObjects("Chart 1").Width
H = ActiveSheet.ChartObjects("Chart 1").Height
TopPos = 0
For Each chtObj In ActiveSheet.ChartObjects
     With chtObj
             .Width = W
             .Height = H
             .Left = 0
             .Top = TopPos
     End With
     TopPos = TopPos + H
Next chtObj
End Sub

'You refer to the chart object something like this:

     ActiveSheet.ChartObjects(1) ' the first chart object in the active sheet

     ActiveSheet.ChartObjects("MyChart") ' a chart that has been named "MyChart"

     ActiveChart.Parent ' the chart object containing the selected chart

'Given that you know your standard sizes, in points (1/72 inch, plus or minus a printer fudge factor of a few 'percent, which
is why God invented trial-and-error), you use this kind of approach:

     With ActiveChart.Parent
         .Height = 325 ' resize
         .Width = 500  ' resize
         .Top = 100    ' reposition
         .Left = 100   ' reposition
     End With

'Suppose I want to line up a chart to cover a range in the worksheet. I can do this very easily using a range 'object
variable and chartobject variable:

     Sub CoverRangeWithAChart()
         Dim RngToCover As Range
         Dim ChtOb As ChartObject
         Set RngToCover = ActiveSheet.Range("D5:J19")
         Set ChtOb = ActiveChart.Parent
         ChtOb.Height = RngToCover.Height ' resize
         ChtOb.Width = RngToCover.Width   ' resize
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
     End Sub

'You can carry out this procedure a little further, using some of your best 6th grade algebra, to line up the 'charts on your
worksheet:

     Sub LineUpMyCharts()
         Dim MyWidth As Single, MyHeight As Single
         Dim NumWide As Long
         Dim iChtIx As Long, iChtCt As Long

         MyWidth = 200
         MyHeight = 150
         NumWide = 3

         iChtCt = ActiveSheet.ChartObjects.Count
         For iChtIx = 1 To iChtCt
             With ActiveSheet.ChartObjects(iChtIx)
                 .Width = MyWidth
                 .Height = MyHeight
                 .Left = ((iChtIx - 1) Mod NumWide) * MyWidth
                 .Top = Int((iChtIx - 1) / NumWide) * MyHeight
             End With
         Next
     End Sub
Thanks a lot for your help in advance.

I am creating a database that automatically creates a chart. Prior to creating the chart, the database will delete the existing chart and create a new one to avoid having x amount of duplicate charts all over the spreadsheet. There will and only be one chart created from the database.

I would like to be able to write code that detects if the chart is present, if not, then go onto another line of code. Sometimes, there will be a case where the database cannot create a chart because there is no supporting data. I would also like to put in a safe measure in case somebody accidently deletes the chart prior to running the code. I tried to put together some code but it didnt work primarily because if the chart is not there then the code doesnt know what and where to look for.


	VB:
	
 
    Goto 13 
Else 
    ActiveWorkbook.Sheets("Data").Activate 
    ActiveSheet.ChartObjects("MyChart").Activate 
    ActiveChart.ChartArea.Select 
    ActiveWindow.Visible = False 
    Selection.Delete 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks,

al

I need to place multiple charts at specifice locations in a worksheet.
The chart locations depend on the column headers of the source data.
My problem is that the range statement does not allow for variable cell
references.
I have tried several variations of Range. The only reference that works is
the format "LetterNumber" as in Range("A1")
Range(Cells(x,y)) does not work
Range(Cells(x,y),Cells(x,y)) does not work
Range("A" & x) does not work
var1="a" & str(x)
Range(var1) does not work.
I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate
Set ChtObj = ActiveSheet.ChartObjects("MyChart")

ChtObj.Top = Range(cells(p,q)).Top
ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop

Hello. I am an excel newbie. I am using John Walkenback’s book called “Excel Charts” to include mouseover events for an embedded chart. I am having a slight issue with the code in my workbook. When the mouse pointer is positioned over a column, a textbox in the upper left of the chart should appear as a “custom tool tip” from data in a range on the spreadsheet. I copied the code from the companion CD with the book and modified it accordingly. My workbook consists of several worksheets. Each sheet has at least 5 embedded charts. My VBA is not reading the range of the cell comments. No results are returned or displayed. I modified the code from the book to suite my needs. Below is the code for the module.


	VB:
	
 Class1[/FONT] 
 
[FONT="]  Sub ConnectChart()[/FONT] 
[FONT="]      Set myClassModule.MyChart = ActiveSheet.ChartObjects("pChart").Chart[/FONT] 
[FONT="]      ActiveSheet.ChartObjects("pChart").Activate[/FONT] 
[FONT="]  End Sub[/FONT] 
 
[FONT="]  Sub DisconnectChart()[/FONT] 
[FONT="]      Set myClassModule = Nothing[/FONT] 
[FONT="]     Range("G1").Select[/FONT] 
[FONT="] End Sub[/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is the code for the Class Module.


	VB:
	
[FONT="]Option Explicit[/FONT] 
[FONT="]Public WithEvents MyChart As Chart[/FONT] 
 
 
[FONT="]  Private Sub MyChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _[/FONT] 
[FONT="]    ByVal x As Long, ByVal y As Long)[/FONT] 
[FONT="]      Dim ElementId As Long[/FONT] 
[FONT="]      Dim arg1 As Long, arg2 As Long[/FONT] 
[FONT="]      Dim NewText As String[/FONT] 
[FONT="]      On Error Resume Next[/FONT] 
[FONT="]      MyChart.GetChartElement x, y, ElementId, arg1, arg2[/FONT] 
[FONT="]      If ElementId = xlSeries Then[/FONT] 
[FONT="]          [B]NewText = Sheets("Sheet5").Range("Comments").Offset(arg2, arg1)[/B][/FONT] 
[FONT="]     Else[/FONT] 
[FONT="]         NewText = ""[/FONT] 
[FONT="]         ActiveChart.Shapes(1).Visible = False[/FONT] 
[FONT="]   End If[/FONT] 
[FONT="]   If NewText  MyChart.Shapes(1).TextFrame.Characters.Text Then[/FONT] 
[FONT="]       ActiveChart.Shapes(1).TextFrame.Characters.Text = NewText[/FONT] 
[FONT="]       ActiveChart.Shapes(1).Visible = True[/FONT] 
[FONT="]   End If[/FONT] 
[FONT="]  End Sub[/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I think my issue is with the line in bold. I graciously appreciate any assistance any of you can provide. If I posted or referenced my sources incorrectly, I apologize in advance. Workbook is attached.

Walkenback, John.Excel Charts. Indiana: Wiley, 2003

I was wondering if anyone knew how to turn off the error in excel, or if there was some method in my macro that I am missing. The error goes away if i delete and then put in the legend again manually, but not if I do it via my macro.

This so far is what I've tried:


	VB:
	
 
Dim count As Integer 
count = 3 
 
 
ActiveSheet.ChartObjects("Chart 18").Activate 
If 0 < ActiveChart.SeriesCollection.count Then 
    ActiveChart.Legend.Select 
    Selection.Delete 
End If 
Do Until 0 = ActiveChart.SeriesCollection.count 
    ActiveChart.SeriesCollection(count - 2).Delete 
Loop 
 
Do Until count >= Sheets.count 
     
    ActiveChart.SeriesCollection.NewSeries 
    ActiveChart.SeriesCollection(count - 2).Name = Sheets(count).Name 
    ActiveChart.SeriesCollection(count - 2).Values = Sheets(count).Range("J2:J3") 
    count = count + 1 
Loop 
If 0 < ActiveChart.SeriesCollection.count Then 
    ActiveChart.SetElement (msoElementLegendLeft) 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The goal was to add multiple series into a chart from multiple sheets. Since the sheets change all the time I thought that I needed to start the charts from scratch each time. I thought about just deleting the chart and making a totally new one everytime, but was stuck here from not knowing how to manage the "ActiveSheet.ChartObjects("Chart 18").Activate" line since the chart number always changes.

Thanks for your help,

James

When i run this code i get run time error 1004
with message - method 'rows' of object_global failed

I dont know what this means could someone explain.

The code below is simply to set the data range for a chart in a chart sheet.
Using data in sheet "Vendor A Grade Chart"
Column A and B row row 2 to the last row with data in those columns.


	VB:
	
 ChartsheetdatarangeA() 
Dim LR As Long: 
     'Get the Vendor chrt sheets to use the correct data range
    LR = Range("A" & Rows.Count).End(xlUp).Row 
    Sheets("Vendor A Grade Chart").Select 
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Sheets("Vendor Data A Grade").Range("A2:B" & LR) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you


	VB:
	
 Workbook_Open() 
     'Application.ScreenUpdating = False ' Turns off screen updating
     
    Dim Filename As String 
    Dim nFam As Integer 
    Dim cRow As Integer 
    Dim i As Integer 
    ws As Worksheet 
     
    Filename = "O:BVRMusers_sharedJCIntraday ToolFamily List.xls" 
    Workbooks.Open Filename 'Opens Family List
     
     'Count the number of rows (Families) in Filename (Family List)
    Set ws = ActiveSheet 
    With ws 
        .Cells(1, 1).Select 
        cRow = .UsedRange.Rows.Count 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i cannot get ws to be set as the ActiveSheet. i receive a run time error 91 each time and cannot figure out what is wrong. i've used this code before under subform functions, but this is the first time i've placed it in the "ThisWorkbook" area. any help would be appreciated.

Quick snippet to cycle through all charts on activesheet and perform some formatting to them. In this case I am formatting the title.


	VB:
	
 Cyclecharts() 
     'USE - Cycle through charts and perform some formatting
     
     'DEC VARS
    Dim i As Integer 
    Dim ActChart As String 
     
     'CODE
    On Error Resume Next 'In case Chart Object Error
    For i = 1 To ActiveSheet.ChartObjects.Count 'SET UP LOOP TO CYCLE CHARTS
         '**********LOOP************************
        Range("A2").Select ' ENSURE NO OTHER CHART IS ACTIVATED
        ActChart = ActiveSheet.ChartObjects(i).Name 'STORE CHART NAME
        ActiveSheet.ChartObjects(i).Activate 'ACTIVATE THE CHART
        ActiveChart.ChartArea.Select 'SELECT CHART AREA
        ActiveChart.ChartTitle.Select 'SELECT TITLE
        With Selection.Font 'FORMAT TITLE
            .Size = 8 
        End With 
         '*********END LOOP **********************
    Next i 'NEXT CHART
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi.

I have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.

However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class

The code where the debugger stops is the last line below here.

	VB:
	
lngKpi = Sheets("Helpfile").Cells(lngRow, 2) 
Sheets("Charts").Select 
ActiveSheet.ChartObjects("DK").Activate 
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.

Hope someone can help me make the macro run through all keyfigures without crashing!

btw; thanks to Jon on http://peltiertech.com/ for a lot of inspiration when making this macro!

Rgs
Karsten

I have the following code for a sheet in my workbook that has 3 charts:


	VB:
	
 Range) 
    Application.Calculation = xlCalculationManual 
     
    ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange 
    ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange 
    ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange 
     
    Application.Calculation = xlCalculationAutomatic 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?

Hi, I'm having a problem with this bit of code...

	VB:
	
Worksheets(1).Activate 
ActiveSheet.ChartObjects("Chart 3").Activate 
 
With ActiveChart 
    .Type = xlLine 
     
    With .SeriesCollection 
        [color=red].Add Source = Worksheets(2).Range("C:C")[/color] 
    End With 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm getting a data mismatch error on the line I have highlighted.

I am unfortunately not well versed in vb or vba, and have been fighting a losing battle with the on-line hindrance.

Any help would be most appreciated...

HK

Morning all,

I have created a spreadsheet in Excel 2007 that works just fine. However, when run using Excel 2003 the code fails in a couple of places. My original code is:


	VB:
	
 Export() 
     '
     ' Export Macro
     '
     ' Keyboard Shortcut: Ctrl+x
     '
    Sheets("Menu").Select 
    Sheets("Menu").Copy 
    ActiveSheet.ChartObjects("chtSales").Activate 
    ActiveChart.PlotArea.Select 
    ActiveSheet.ChartObjects("chtSales").Activate 
    ActiveChart.ChartArea.Select 
    ActiveSheet.ChartObjects("chtSales").Activate 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Sales Monthly" 
     'ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
     'ActiveChart.SetElement (msoElementChartTitleAboveChart)
     'ActiveChart.ChartTitle.Text = Sheets("Menu").Range("H6").Value
     'ActiveChart.ChartArea.Select
    Sheets("Menu").Select 
    ActiveSheet.ChartObjects("chtDaily").Activate 
    ActiveSheet.ChartObjects("chtDaily").Activate 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Sales Daily" 
     'ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
     'ActiveChart.SetElement (msoElementChartTitleAboveChart)
     'ActiveChart.ChartTitle.Caption = Sheets("Menu").Range("Q6").Value
     'ActiveChart.ChartArea.Select
    Sheets("Menu").Select 
    ActiveSheet.ChartObjects("chtStock").Activate 
    ActiveSheet.ChartObjects("chtStock").Activate 
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Stock" 
     'ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
     'ActiveChart.SetElement (msoElementChartTitleAboveChart)
     'ActiveChart.ChartTitle.Caption = Sheets("Menu").Range("H28").Value
     'ActiveChart.ChartArea.Select
    Sheets("Menu").Select 
    Range("O30:U40").Select 
    Selection.Clear 
    Sheets("Menu").Select 
    ActiveWindow.SelectedSheets.Visible = False 
    Sheets("Sales Monthly").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code falls down with the .SetElement method. Additionally on the line:


	VB:
	
ActiveChart.ChartTitle.Text = Sheets("Menu").Range("H6").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get run-time error '1004': unable to set the Text property of the Chart Title class.

What I need is for each chart to show its data table and for the chart title to pull it's data from the 'Menu' worksheet's relevant cells - and obviously be compatible in both Excel 2003 and Excel 2007.

Any help greatly appreciated, as always.

RDF

Hello

I posted a problem on here yesterday and received very useful help (here:http://www.ozgrid.com/forum/showthread.php?t=156937) I now have a new problem with the same macro but I think it is sufficiently different to need a new thread.

What I need to do is to make a chart on the current worksheet, using data from the current worksheet, without specifying which worksheet that will be. The code below worked fine until I moved the macro to the personal.xlsb thing so that it could be used in different workbooks.


	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=100, Width:=700, Top:=200, Height:=500) 
    .Chart.ChartType = xlBubble 
    .Chart.SetSourceData Source:=Range( _ 
    strDateRange & "," & _ 
    strPwinRange & "," & _ 
    strEstValRange) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in the current test strDataRange = !$B:$B strPwinRange = !$C:$C and strEstValRange = !$D:$D)
Now, this code gives an error 1004: Method 'Range' of object '_Global' failed. To correct this I have had to do the following (adding sheet1):

	VB:
	
 ActiveSheet.ChartObjects.Add _ 
    (Left:=100, Width:=700, Top:=200, Height:=500) 
    .Chart.ChartType = xlBubble 
    .Chart.SetSourceData Source:=Range( _ 
    "'Sheet1'" & strDateRange & "," & _ 
    "'Sheet1'" & strPwinRange & "," & _ 
    "'Sheet1'" & strEstValRange) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I don't want the worksheet to have to have a specific name for the macro to run, and I thought having the ActiveWorksheet part should make that unnecessary. The original code still works in the original workbook, even when run from personal.xslb, so I don't really know what's going on.

Any help would be greatly appreciated.

Hey everyone!

I am having some issues pasting a linked excel chart into an embedded PowerPoint. I either get an error message or PowerPoint completely shuts down. Any help is appreciated! code is as follows:


	VB:
	
 PastingChart() 
     '
     ' PastingChart
     '
    Dim PPApp As PowerPoint.Application 
    Dim PPPres As PowerPoint.Presentation 
    Dim PPSlide As PowerPoint.Slide 
     '
    Sheets("Report").Select 
    ActiveSheet.Shapes("Object 34").Select 
    Selection.Verb Verb:=3 
    Set PPApp = GetObject(, "Powerpoint.Application") 
    Set PPPres = PPApp.ActivePresentation 
    PPApp.ActiveWindow.ViewType = ppViewSlide 
    Worksheets("May2011_Summary").Select 
    ActiveSheet.ChartObjects("Chart 1").Activate 
    ActiveChart.ChartArea.Copy 
    PPPres.Slides(2).Shapes.PasteSpecial ppPasteOLEObject, DisplayasIcon = False, , , , Link = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


hi all, im getting this error when i run a macro to remove a chart in a worksheet.

code below to create chart & remove chart:

	VB:
	
 leadchart() 
     '
     ' leadchart Macro
     ' Macro recorded 14/07/2005
     '
     
     '
    Range("K3").Select 
    Charts.Add 
    ActiveChart.ChartType = xlPie 
    ActiveChart.SetSourceData Source:=Sheets("Input Sheet").Range("K3") 
    ActiveChart.SeriesCollection.NewSeries 
    ActiveChart.SeriesCollection(1).XValues = "='Input Sheet'!R15C2:R27C2" 
    ActiveChart.SeriesCollection(1).Values = "='Input Sheet'!R15C4:R27C4" 
    ActiveChart.SeriesCollection(1).Name = "='Input Sheet'!R10C1:R10C2" 
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Input Sheet" 
    ActiveWindow.Visible = False 
    Windows("ChannelV7.xls").Activate 
    Range("N11").Select 
End Sub 
 
 
Sub removechart() 
     '
     ' removechart Macro
     ' Macro recorded 14/07/2005
     '
     
     '
    ActiveSheet.ChartObjects("Chart 24").Activate 
    ActiveChart.ChartArea.Select 
    ActiveWindow.Visible = False 
    Selection.Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
error says:' run-time 1004, unable to get chartObjectProperty of the worksheet class'.

any help pls?

I have a chart in SHARED Workbook
I am getting error "Run time Error: Method of visible chartobjects failed.

Is there a way to make the CHART work in SharedBook too?
Current Code

	VB:
	
[FONT=Courier New]Sub UpdateChart() 
Dim ChtObj As ChartObject 
Dim UserRow As Long 
Set ChtObj = ActiveSheet.ChartObjects(1) 
UserRow = ActiveCell.Row 
If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then 
    ChtObj.Visible = False 
Else 
    ChtObj.Chart.SeriesCollection(1).Values = _ 
    Range(Cells(UserRow, 2), Cells(UserRow, 6)) 
    ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text 
    ChtObj.Visible = True 
End If 
End Sub 
[/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I get this error "Unable to get the ChartObjects property of the Worksheet class" when my macro tries to execute this code:

ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.PlotArea.Select

It highlights the first line. What am I missing? Thank you!

Windows(WorkbookName1).Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.Visible = False
Windows(WorkbookName1).Activate
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Combining Two Histograms.xls").Activate
Columns("A:B").Select
ActiveSheet.Paste
Windows(WorkbookName2).Activate
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveWindow.Visible = False
Windows(WorkbookName2).Activate
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Combining Two Histograms.xls").Activate
Columns("C:C").Select
ActiveSheet.Paste

WorkbookName1 and WorkbookName2 are variables (string type)

What is the error?

THANKS!

I am getting an error: "Unable to get the ChartObjects property of the Worksheet class"

I am playing around with forms in VBA and am using the code:

Worksheets(SheetName).Activate
ActiveSheet.ChartObjects(ChartID).Activate
ActiveChart.ChartArea.Select

What am I missing?

Hi all,

This is my code. It's supposed to change the XValues as well as Values property of a series of charts (nmbrObjects + 1). The code generates a runtime error in the bold part.

Funny thing is that it doens't do so for x = 2, it starts when x = 3. Apparently it's something to do with the particular chart that is activated when x = 3.

Any clues?

Code:
 

For x = 2 To nmbrObjects + 1

    OBJECTCOLUMN = Sheets("LookupData").Cells(5, x).Value
    Yield = 100 * (Sheets("CLEAN_DATA_PER_DATE").Cells(LAST, OBJECTCOLUMN).Value / Sheets("CLEAN_DATA_PER_DATE").Cells(FIRST,
OBJECTCOLUMN).Value - 1)


    ActiveSheet.ChartObjects("Chart " & x - 1 & "").Activate
    
    With ActiveChart
        .ChartArea.Select 
        .SeriesCollection(1).XValues = _
            "=CLEAN_DATA_PER_DATE!R" & FIRST & "C77:R" & LAST & "C77"        
        .SeriesCollection(1).Values = _
            "=CLEAN_DATA_PER_DATE!R" & FIRST & "C" & OBJECTCOLUMN & ":R" & LAST &  "C" & OBJECTCOLUMN & ""
        .ChartTitle.Characters.Text = Sheets("LookupData").Cells(2, x).Value & "   " & WorksheetFunction.Round(Yield, 2) &
"%"
    End With
     
Next


Hello,

I am using this code to make all the charts in the activesheet to "don't move or size with cells":

Code:
Dim cch as ChartObject
For Each cch In ActiveSheet.ChartObjects
ActiveSheet.cch.Placement = xlFreeFloating 'xlMoveAndSize
Next cch
But it gives me an error and I don't know why.. I have text boxes in the same sheet.

could someone help?

thanks
Andy


No luck finding an answer? You could always try Google.