Selecting a chart using a variable

hennenzac

New Member
Joined
Nov 29, 2014
Messages
2
Hello,

I'm using Win7 and MS Office 2010. What I am trying to do is select a chart that is within a worksheet that has a specific name. I currently name the charts to their according year ex)2014.

In my workbook I have sheets that are also named after a year (2014). When I put the sheet name into a variable (which shows up as 2014 when i debug) I try to use that variable to activate the correct chart which is also named 2014.

Here is the code:
Code:
Sub test()



Sheets("2014").Activate
wsname = ActiveSheet.Name




Avg = Format(Sheets(wsname).Range("AR6").Value, "###")


Sheets("Calf Price Hist.").Activate


ActiveWorkbook.Sheets("Calf Price Hist.").ChartObjects(wsname).Activate


With ActiveChart
        .ChartTitle.Text = wsname & " - Price / Calf " & Chr(13) & "Average:   $" & Avg
            
        .ChartTitle.Format.TextFrame2.TextRange.Characters(22, 15).Font.Size = 11
End With
        
End Sub

I get this error: Run-time error '1004': Application-defined or object defined error. Which is happening on this line:

Code:
ActiveWorkbook.Sheets("Calf Price Hist.").ChartObjects(wsname).Activate

I have tried to define the variable as a string, integer, or not defining it at all which always brings up this error. I did find a work around that makes the code work, by changing the line above to this and changing the chart name to "Chart 2014":

Code:
ActiveWorkbook.Sheets("Calf Price Hist.").ChartObjects("Chart " & wsname).Activate

I don't understand why one works and the other doesn't. I'm using a variable to define the chart in both except adding "Chart " to the name fixes it. I have recorded a macro selecting a chart named "2014" only; and it shows the chart name to be "2014" so it isn't necessary to have "Chart" in the name. Also if I manually type "2014" in the ChartObjects in the code, this will work as well.

Again I got it to work, but I want to know why it doesn't work and is there a way to leave the charts named only "2014" rather than "Chart 2014" which would make my code simpler in other macros.

This is my first time on this forum so I hope I did everything correct...but correct me if there is something I did wrong.
icon7.png


Thanks!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi and welcome to the MrExcel Message Board.

I think the problem is that you can use ChartObjects to find a chart by an index number or by a name. Excel is, helpfully(!), converting your "2014" into 2014 and then looking for a chart with an index number of 2014. Presumably, you don't have one so it then complains.

You can check this by trying to find chart name = "1". It will find the chart with an index of 1 i.e. your first created chart.

All I can suggest is, don't use any name that can be confused with a number or do the look up from name to number your self.

For instance, this works:
Code:
Sub x()

    Dim chName As String
    
    chName = "2014"
    Sheet1.ChartObjects(GetIndex(chName)).Activate
    
    chName = "Chart 1"
    Sheet1.ChartObjects(chName).Activate
    
    chName = "Chart 1"
    Sheet1.ChartObjects(GetIndex(chName)).Activate
    
End Sub

Function GetIndex(Name As String) As Long
    Dim c As ChartObject
    For Each c In Sheet1.ChartObjects
        If c.Name = Name Then
            GetIndex = c.Index
            Exit Function
        End If
    Next
End Function
The function GetIndex looks through the list of names in Sheet1 and returns the index number if there is a match.
 
Upvote 0
Okay, that makes sense. It appears I could just name my charts with some sort of text so it doesn't think it's an index. I have code creating the charts anyway so it won't be too bad to have it name charts with some text in it. Either way I'm glad I understand why it wasn't working. I really like that function you came up with for finding the name...very clever! I'm pretty new to vba so it took me a while, but I was able to follow what you are doing in the function. I knew you could pass variables to/from a function but I didn't know you could make the function name itself a variable. Also I didn't know you could use a variable as part of vba code ex) c.Name

Thank you very much for your help! Much appreciated!
 
Upvote 0
Yes, calling the charts something like C2014 or YR2014 should make the issue go away.

Glad you liked the function. Function name always "become the variable" in VBA. Once you va e spotted that you should be able to see lots more e.g. Sin, Cos and Tan or Left, Right and Mid are all used in VBA. When Excel does not provide one you want you can write your own.

Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top