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.)

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

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