Is it possible to find/identify formula links in chart titles?


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rate Thread Display Modes
  #1  
Unread 9th September 2010, 12:38
ImprovingOldie's Avatar
ImprovingOldie ImprovingOldie is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Some VBA experience
 
Join Date: Sep 2010
Location: London, England
Posts: 23
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 7
Thanked 0 Times in 0 Posts
Reputation: 10 (7) ImprovingOldie is on a distinguished road
England
Post Is it possible to find/identify formula links in chart titles?
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

Hello All,

My name is Ian, and new to 'the code cage'. I live in London, England, am a self employed market research analyst and self taught in Excel vba with the help of a few books.

I did some professional programming back in the 70's using assembler/machine code in small computers. In the last couple of years, I thought I would automate some of my office tasks and have become reasonably proficient - although orientating to object oriented code wasn't natural for me! I haven't had much opportunity to swap/share ideas as I work by myself and I hope this forum can help me widen my approach and introduce me to new techniques; and maybe I can help others with my quirky experience.

To kick off, I have something that is challenging me and would like to know if it is possible to identify dynamic links in charts. My code is processing a workbook full of chart sheets where each chart has a title linked to a cell elsewhere in the workbook (this cell is a formula which constructs a string with several variables), I would like to be able to locate the source cell using the chart object but cannot identify any property that would do the job. Both '.characters' and '.text' offer up the displayed content of the chart title.

I am using MS Office XP (Excel 2003 sp3). Has anyone a solution for this?

Ian.

The Code Cage Advertisment
Advertisement
  #2  
Unread 9th September 2010, 12:42
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 52
Posts: 11,880
Posts Per Day: 3.66
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (193) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Welcome to the fourms Ian,

Why not add a workbook?
Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query

Attachments.
Information

Information


To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload, if you have any trouble please use this Contact Us link or the one at the bottom of the any page.



__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!



  #3  
Unread 9th September 2010, 13:58
ImprovingOldie's Avatar
ImprovingOldie ImprovingOldie is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Some VBA experience
 
Join Date: Sep 2010
Location: London, England
Posts: 23
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 7
Thanked 0 Times in 0 Posts
Reputation: 10 (7) ImprovingOldie is on a distinguished road
England
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Thank you for the welcome Simon.

To further clarify, on worksheet 1, let's say for instance that there is a cell ("$D$50") with a formula that constructs a string containing sales month with the market value for laser printers. In the same workbook there is a line-chart plotting brand sales shares over the last 12 months which has its chart title set up as "=sheet1!$D$50". I am seeking a vba method to examine the chart title and ascertain its source (i.e. "=sheet1!$D$50") , not the source contents (e.g. "June 2010 Market Value 200M.")

Using 'ObChart.ChartTitle.Text' returns "June 2010 Market Value 200M."

The Code Cage Advertisment
Advertisement
  #4  
Unread 9th September 2010, 14:20
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 52
Posts: 11,880
Posts Per Day: 3.66
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (193) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Ian, you may be teaching me something here, i didn't know you could name your chart title like that, infact i don't know how to do it like that other than through vba. Can you post a sample workbook?
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!



  #5  
Unread 9th September 2010, 16:14
ImprovingOldie's Avatar
ImprovingOldie ImprovingOldie is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Some VBA experience
 
Join Date: Sep 2010
Location: London, England
Posts: 23
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 7
Thanked 0 Times in 0 Posts
Reputation: 10 (7) ImprovingOldie is on a distinguished road
England
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Hi Simon, here is a workbook with the chart titles linked to worksheet cells and formulas. With vba code, I am trying to identify the source cell using the charttitle object but without success.
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.

  #6  
Unread 9th September 2010, 23:19
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.25
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


hi Ian,

I started reading this thread & thought to myself, "nah, surely there's a way to find the link of a chart title in VBA", but I've tried everything you've mentioned - looking through the Object Browser, searching for a sub-property of the caption/characters/text, even turning off the calculation & changing to R1C1 referencing. To no avail!

However, with a little thinking outside the box I'm sure we can get you a solution...
The following may be slow I suggest:
(please overlook my shorthand scattering of dots to represent a property belongs to something)
- storing the .text eg ChrtTitleStr = .text
- using a modification of something like Find All to loop through the real used range of each of the worksheets in the workbook (hopefully the Title is not populated with an external link - if that's even possible?) & search for ChrtTitleStr.
- store the .formula string of each found cell that matches with the title
- temporarily change each foundcell one at a time & check if the .title changes (test what you actually need to Calculate because code may run quicker if you turn the calculation off)
- change the foundcell back to its original .formula
- if the chart.title changes, perhaps exit the loop because it's likely you've found your cell
- if the files do, or are likely to, have various cells that could link to each other & have the same value, then build a subrange of matching cells & perform a loop or check for dependencies to ensure that you get the final cell in any dependency chain.
- if not, keep looping, matching cell by matching cell, & then worksheet by worksheet...

btw, this approach may be slooooooowwwww depending on the size of the used ranges & the number of worksheets in your file
Give it a go & let us know if you need any help.


hth
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

The Following User Says Thank You to broro183 For This Useful Post:
ImprovingOldie (10th September 2010)
The Code Cage Advertisment
Advertisement
  #7  
Unread 9th September 2010, 23:41
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.25
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


If you're looking for any other information on Charting I recommend starting here:
Excel Charts and Tutorials by Peltier Technical Services
Or checking out any posts by Andy Pope.

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

  #8  
Unread 10th September 2010, 00:25
Zack Barresse's Avatar
Zack Barresse Zack Barresse is offline
Junior Member
MS Office Version: Ms Office 2010
MS Office Skill Level: I'ma learnin
 
Join Date: Feb 2009
Location: Oregon, United States
Age: 37
Posts: 40
Posts Per Day: 0.01
My Top Tip Count: 1
Thanks to others: 0
Thanked 4 Times in 4 Posts
Reputation: 60 (9) Zack Barresse will become famous soon enough
1 Top Tips
USA
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Chart.ChartTitle.Formula

If you have a formula as your chart title, this will return it. Is this what you're looking for?

  #9  
Unread 10th September 2010, 01:14
broro183's Avatar
broro183 broro183 is offline
The Code Cage Staff
MS Office Version: Excel 2007 @ home & 2010 @ work
MS Office Skill Level: Can use VBA
 
Join Date: May 2009
Location: Waikato, New Zealand
Posts: 742
Posts Per Day: 0.25
My Top Tip Count: 0
Thanks to others: 15
Thanked 50 Times in 50 Posts
Reputation: 801 (24) broro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to beholdbroro183 is a splendid one to behold
New_Zealand
Default Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


hi Zack,

Which version of Excel does this work in?

I thought it should but I couldn't & still can't get it to work
I'm using Excel 2007 & the below code chokes on your suggested syntax when run against the "chart1" sheet of the OP's file. Is it because the file is saved as an xls?
Or, perhaps, is it because I should be referring to a "ChartObject" (ie the container) rather than the "ActiveChart"?

VBA CODE:
Option Explicit 
 
Sub test() 
    With ActiveChart 
        If .HasTitle = True Then 
            With .ChartTitle 
                Debug.Print .Caption 
                 'returns "Total Market  (548k units) - December 2009"
                Debug.Print .Text 
                 'returns "Total Market  (548k units) - December 2009"
                Debug.Print .Formula 
                 'returns "Error 438: Object or property not supported."
            End With 
        End If 
    End With 
End Sub 

If your syntax does work, it would be much quicker than my suggested work around & it's in a one liner too.

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #10  
Unread 10th September 2010, 01:42
ImprovingOldie's Avatar
ImprovingOldie ImprovingOldie is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Some VBA experience
 
Join Date: Sep 2010
Location: London, England
Posts: 23
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 7
Thanked 0 Times in 0 Posts
Reputation: 10 (7) ImprovingOldie is on a distinguished road
England
Exclamation Re: Is it possible to find/identify formula links in chart titles?

------ Register to get rid of these "In Post" ads! ------


Thanks Rob,

Looking at your solution I can see that you're a guy that doesn't like giving up. I was hoping for clean/simple solution and guess that it's not there; on the bright side at least I wasn't overlooking anything (being stupid). I'll let you know if I go down the convoluted route to solve this.

Thanks again,

Ian.

------------------------------ posted before receiving a reply after 11 minutes of original post ------------------------------

Hi Zack,

Your solution looked promising but I'm getting the same result as Rob. There's nothing in my object model that has .formula supporting chart.charttitle or its properties. I'd would love to be proved wrong!

Thanks,
Ian.


Last edited by ImprovingOldie; 10th September 2010 at 01:42.
The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
chart, find or identify, formula, links, titles

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 18:09.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University