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...o&userid=35923
View this thread: http://www.excelforum.com/showthread...hreadid=557726

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


--
victorcamp
------------------------------------------------------------------------
victorcamp's Profile: http://www.excelforum.com/member.php...o&userid=35923
View this thread: http://www.excelforum.com/showthread...hreadid=557726