Chart Documenting Via VBA Questions

Excel 2003

I've got a -lot- of Excel charts to document. I'm doing it all via VBA
procedures and saving the results into an Access table. Access VBA is
my main proficiency, so I've got to learn a lot about the Excel Object
Model. I've tracked down a lot of data, but here are a couple I just
can't find.

1. The formula used in a ChartTitle. I see it in the formula bar when
it's selected, but everywhere I look it only returns the currently
showing text: the formula result. I *need* the formula, and I've got to
get it using VBA. It's obviously saved somewhere!

2. The value that will be returned by each Option Button in each group
of Option Buttons. I see it in the LinkedCell (1, 2, 3, 4, etc., as I
click each button), but no matter where I try in VBA, all I get is 1
for checked and -4146 for unchecked. Surely Excel can tell me what that
button will return. In Access all I'd have to do is
![optionbutton].OptionValue. (I'd also like to be able to change it,
but that may be asking too much!)

I realize #2 could be retrieved indirectly by looping through the
buttons, checking each, then reading the result from the cell, but it's
got to be somewhere. For #1 I'd even settle for jumping to the formula
bar and using API calls to put it into, then retrieving it from the
Clipboard, but I don't even know how to get to the Formula bar.

I can see by my research that question #1 has been asked before, but
nowhere could I find that it was answered. Any help would be greatly
appreciated.


--Vic

(Also tried: PUP v6 Chart Report returns result, not formula.)


-- 
victorcamp
------------------------------------------------------------------------
victorcamp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35923
View this thread: http://www.excelforum.com/showthread.php?threadid=557726

0
7/2/2006 11:50:31 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
802 Views

Similar Articles

[PageSpeed] 15

Hi,

1. I don't believe it is possible to get at this information. You are 
correct that it is saved somewhere but the location is not exposed by 
the object model.

2. Looking in the linked cell is the quickest way to get which button is 
selected. Otherwise you will need to loop through each control and jump 
out of the loop once you find the selected button.

Cheers
Andy

victorcamp wrote:
> Excel 2003
> 
> I've got a -lot- of Excel charts to document. I'm doing it all via VBA
> procedures and saving the results into an Access table. Access VBA is
> my main proficiency, so I've got to learn a lot about the Excel Object
> Model. I've tracked down a lot of data, but here are a couple I just
> can't find.
> 
> 1. The formula used in a ChartTitle. I see it in the formula bar when
> it's selected, but everywhere I look it only returns the currently
> showing text: the formula result. I *need* the formula, and I've got to
> get it using VBA. It's obviously saved somewhere!
> 
> 2. The value that will be returned by each Option Button in each group
> of Option Buttons. I see it in the LinkedCell (1, 2, 3, 4, etc., as I
> click each button), but no matter where I try in VBA, all I get is 1
> for checked and -4146 for unchecked. Surely Excel can tell me what that
> button will return. In Access all I'd have to do is
> ![optionbutton].OptionValue. (I'd also like to be able to change it,
> but that may be asking too much!)
> 
> I realize #2 could be retrieved indirectly by looping through the
> buttons, checking each, then reading the result from the cell, but it's
> got to be somewhere. For #1 I'd even settle for jumping to the formula
> bar and using API calls to put it into, then retrieving it from the
> Clipboard, but I don't even know how to get to the Formula bar.
> 
> I can see by my research that question #1 has been asked before, but
> nowhere could I find that it was answered. Any help would be greatly
> appreciated.
> 
> 
> --Vic
> 
> (Also tried: PUP v6 Chart Report returns result, not formula.)
> 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
7/4/2006 8:55:37 AM
Thanks for a reply.

I have become so used to Access, where I can find just about
everything! I would certainly suggest to Microsoft that they add these
two items to the Excel object model in a future version.

Nonexistant, but needed:
ChartTitle.RefersTo
Shapes.OptionValue

For the ChartTitle, I wil perhaps explore trying to get to the Formula
bar and reading its contents. If I discover anything, I'll put it
here.

--Vic


-- 
victorcamp
------------------------------------------------------------------------
victorcamp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35923
View this thread: http://www.excelforum.com/showthread.php?threadid=557726

0
7/4/2006 6:16:36 PM
Reply:

Similar Artilces:

Inserting charts into many workbooks
Hi, I'm using Excel 2007 and am pretty new to VBA. I have a folder with 40 or so workbooks- all with the same worksheet table format. The worksheets are huge (20 000 + rows, 50 or so columns). I need to open a workbook, make several fully formatted graphs, then insert the same graphs into every workbook , updated them with that workbooks' data. Any ideas on code for this? This isn't fully automated, but it will save some time. In one workbook, create the chart and format it to your specs. Then paste the chart into another workbook, and use the utility linked to i...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

2 Questions
Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column <> 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell ...

Exchange 5.5 to Exchange 2003 Migration Question: No ADC??
Hello all: I have an environment where I need do do an Exch 5.5 to 2003 Migration. The domain has already been migrated from NT4 to 2003. The only thing is, is that there is no Active Directory Connector setup anywhere in the environment. That leads me to my first question I guess. How is this possible that Exch 5.5 is even working with AD? In terms of upgrading, what would recommendations be? It is a single Site. I was thinking about bringing up a new Exch 2003 server, and using "Move Mailbox" to the new exch server. Replicate all the necessary public folders, decomission the old ...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Pivot Table Question #15
Hey all, Love pivot tables but i have a question, the table is set up as so Month Pallet# BoxesPur BoxesSold Price Frt Gross Net Jan 1 5 1.0 ..3 Formula Formula 2 5 2.0 .5 3 10 3 .15 Jan Total 10 10 6.0 ..95 So, the problem is in the Jan Total row, is there a way...

How do I create a bookfold document in Word 2007?
I am new to Word 2007. How do I create a document in booklet form? Thanks I'm sure you'll get better answers in an MSWord forum. dadolim wrote: > > I am new to Word 2007. How do I create a document in booklet form? Thanks -- Dave Peterson ...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

I get COMException while exporting chart on IIS 6.0.
I have this code that works perfectly on my development machine when I deployed it at customer site it gets exception. Here is the error and my code: thanks error ========== Exception from HRESULT: 0x800A03EC. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC. Source Error: An unhandled exception was generated during the execution of the c...

Mock question?
Hi all Not sure if this is the right group, I am happyo to repost to correct group if I know what that is! but in the mean time - here goes I am using NMock2 and I have question. I have the following interface: public interface ITime { int Hour { get; } } and the following weather service: public class WeatherService:ITime { public string GetTodaysWeather() { if (Hour >= 0 && Hour <= 6) { return "Sunny"; } else if (Hour >= 7 &&...

chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under Vista. I can select a line style, for example, long dashes. However, if I try to change the axis (change from "automatic" to "fixed" on the horizontal axis), the line on the chart immediately becomes solid again. The legend still shows the proper dashing. I can get the dashing partly back by making the line thinner, but only where the variation is fastest - regions where the derivative is near zero are still solid even for thin lines. I'll appreciate any help! frank I was not able to reproduce this. Can you...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

Updating Related Entities Via Workflow
I'm a complete newbie to workflows, so your help is much appreciated. In general my question is, can a parent entity be updated via workflow rules when some event occurs on a related child entity, or does this need to happen via assembly or callout? Specifically... I have a situation where leads come into our system from various places. Because I cannot add leads to a queue, I use a workflow to create a phone call for each lead and add it to a queue. Now, is it possible (through workflow) that when a user accepts the phone call activity from the queue, that the parent lead own...

Printing Word and excel documents via excel vba
I have a document that has some Excel and some word content i would like to use excel vba to print both a page from word and then a page from excel. I know how to select and open the word document, however i cant find any code to print page X from word. basically i need to: print page 1 of c:\xx\document1.doc print page1 of sheet1 of c:\yy\excelwkbk1.xls print page 2 of c:\xx\document1.doc print page2 of sheet1 of c:\yy\excelwkbk1.xls and so on it will require moving between word and excel - the excel bit i get, but getting back to excel from word, and printing out in word is slightly more...

Inconsistent Chart Sizing Into PPT
Can't get charts to paste into PowerPoint with consistent size. To test, I expanded (using the corner handles) two charts to exactly fill the screen. When I copy/paste each chart into an individual slide in PowerPoint, one is noticibally larger than the other. Is there a way to make the charts the same size and paste the same size? I have an article on this in the works, due out in about 24 months, the rate I'm going. A. Use embedded charts, and use Copy Picture (hold shift while selecting Edit menu, os Copy turns to Copy Picture) with On Screen and As Picture options. B. ...

Charting dynamic range
Ok let me say i fully understand how to name a range and in the source data use the formula =sheetname!rangename Problem arrises when my macro that runs an advanced filter with the source data and add data to the bottom of the range the chart reverts back to it's initial data source =sheet1!$a$4:$b$15 so even though it accept the named range intitailly once the advanced filter runs it almost ommits the named range. I am using 2007 and i never had this problem in 2003. What suggestions do you have for me? Thank you!!!!! -- Thank you, Jennifer ...

Installation question 06-05-07
I have one server with sql2005 running MOSS 2007 (sharepoint) I would like that sql server also to run CRM dynamics 3.0 pro. We are only 5 users and its a rather fast server so we believe it will go fine performance vice. Can I install SRS from the crm3 disk on a SQL2005 ? does it NEED default website and port 80 ? (the MOSS are using that) any help appreciated Hello, I suggest install SQLServer 2005 Reporting Services (SRS) manually using the SQLServer 2005 installation media. Configure SRS to use a different (new) website, having its own application pool. Then during CRM setup speci...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

Default font in charts
Is there any way to change the default font in charts? ie, I'd like every axis and title to be 16pt Times New Roman right off the bat. Thanks. Alex "Alex" <Alex@discussions.microsoft.com> wrote in message news:6096B69F-DCF5-4BB8-8158-433A4A416053@microsoft.com... > Is there any way to change the default font in charts? ie, I'd like every > axis and title to be 16pt Times New Roman right off the bat. Thanks. Set up a chart the way you want it to look. Right-click in its chart area and choose Chart Type. Click the Custom Types tab and hit the "set as default...

Dynamic chart labels
I am using a dynamic bar chart and want to add a label to an individua data point. Is there a way to do this so that the label follows th data point as the chart updates -- danoPosted from http://www.pcreview.co.uk/ newsgroup acces A datalabel will follow the datapoint and update as appropriate. To add data labels to a series, double click the series and click on the Data Labels tab. To add data labels to a single point, select the series then the point (two single clicks), then double click on the point, and again click on the Data Labels tab. - Jon ------- Jon Peltier, Microsoft Ex...

show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Sales Document Inquiry Zoom
Has anyone ever seen after an order is fulfilled - the sales document detail inquiry zoom for the order is blank - doesn't even show the order information (which is standard gp). Header info on that window does have type id, document number; master number = 0; remaining subtotal=$; times repeated = 0 If i look at the sales quantity status inquiry zoom - qty to order = 1; qty to invoice = 1; Remaining qty=1 Quantity Allocated = 1 Quantity Fulfilled = 1 And the order will not transfer to invoice = no errors; the sales transfer log just says 0 documents transferred. Yes, I have seen...

Dynamic chart: Changing Ranges
Hi: I have a set of data, going across by months, and going down into different categories that I have to graph using stacked column charts. Since the data is going to be in the same columns, just going down a category...I'd like to know if there is a way for me to add some kind of drop-down to the chart so that users can just drop-down to CATEGORY 1, and see the chart for CATEGORY 1, without me having to build 10 different charts that i'll have to update everytime i add new data. I would appreciate any advice or feedback. Hello: Check out Jon Peltier's website. It looks ...

2 fonts in Chart title in E2007
We are tranferring code from 2003 to 2007 and noticed a change in how VBA reads the following line: ActiveChart.ChartTitle.Characters(5,2).Font.Color = vbred IN 2003, it reads it correctly coloring letters 5 & 6 red. in 2007, it colors the whole Chart title red. It seems that regardless of the start and length I pass into the characters function, the formatting occurs to the entire chart title, not just the characters I wanted. Even recording the VBA in 2007 yields no help as it cannot record the partial font change, even though the GUI allows it. IN 2003 you can reco...