Option Buttons created with Forms

[Barb Reinhardt]

I have a document that has a bunch of option buttons created with the forms
toolbar. How do I access the name of the option button iand the "value"
(true/false) of the option button n VBA?

I have this so far and I'd like to get the OptionButton name somehow, but
haven't been able to get to it.

Dim myShape as InlineShape

For Each myShape In myDoc.InlineShapes

If myShape.OLEFormat.ProgID Like "*OptionButton*" Then
'Do something
End If
Next myShape

Thanks,
Barb Reinhardt

 

[ker_01]

Hi Barb-

Here is a code snippet I used to pull data from a template into Excel:
WhichCol = 0
PasteRow = Find_Last(Sheet1) + 1
Dim ffld As Word.FormField
For Each ffld In wrdDoc.FormFields
WhichCol = WhichCol + 1
ConvertCol (WhichCol)
Debug.Print ffld.Result
Sheet1.Range(UseCol & PasteRow).Value = ffld.Result
Next

If you only want to pull the optionbutton data, you can modify this code for
optionbuttons instead of checkboxes:
tRef = "Check" & i
Debug.Print wrdDoc.FormFields(tRef).CheckBox.Value

If you have the ability to change the optionbutton names before they go out,
it will be easier for you to reference individual fields directly, but that's
up to you.

HTH,
Keith

 

[Barb Reinhardt]

That doesn't work. Any other ideas?

Hi Barb-

Here is a code snippet I used to pull data from a template into Excel:
WhichCol = 0
PasteRow = Find_Last(Sheet1) + 1
Dim ffld As Word.FormField
For Each ffld In wrdDoc.FormFields
WhichCol = WhichCol + 1
ConvertCol (WhichCol)
Debug.Print ffld.Result
Sheet1.Range(UseCol & PasteRow).Value = ffld.Result
Next

If you only want to pull the optionbutton data, you can modify this code for
optionbuttons instead of checkboxes:
tRef = "Check" & i
Debug.Print wrdDoc.FormFields(tRef).CheckBox.Value

If you have the ability to change the optionbutton names before they go out,
it will be easier for you to reference individual fields directly, but that's
up to you.

HTH,
Keith

 

[Graham Mayor]

It would help to know *exactly* what has been inserted. The Forms toolbar
implies a version of Word before 2007 and there is no 'options button' on
that toolbar. If you have inserted a check box then the value of the
checkbox is found by

ActiveDocument.FormFields("Check1").CheckBox.Value

where Check1 is the name of the check box field. Right click the field with
the form unlocked and check properties for the field name.

If you are processing forms then the following may be of help
http://www.gmayor.com/ExtractDataFromForms.htm - especially the macros at
the end of the page.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Barb Reinhardt]

I believe it was added in Excel 2007 using Developer and clicking on the
Legacy forms command to get to the Option Button ActiveX control. I can
find no collection that contains the option buttons. They appear to be part
of the InlineShapes collection, but I can't seem to define an "Option Button"
object from inline shapes. I've had to work with each of them separately in
VBA, with is a PITA.

Does that answer your question?

Barb Reinhardt

 

[Graham Mayor]

This forum is for Word vba. Excel has its own vba forum. However if you
right click the OptionButton and View Code (while the form is in Design
Mode) you can define what happens when the button is clicked. It is similar
to programming the controls on a userform..

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Barb Reinhardt]

I misspoke. It was added in WORD 2007. Since I do most everything in
Excel, I goofed. Everything else in my message is the same. I'm trying to
find the collection object for the OptionButtons and can't find anything.
Can you help find that?

 

[ker_01]

Barb-

I went back to some old files to refresh my memory. I had tried adding
non-activeX optionbuttons in W07 via:

Sub MyRadioButton()
Selection.InlineShapes.AddOLEControl ClassType:="Forms.OptionButton.1"
End Sub

but had the same problems you are finding- I couldn't figure out how to get
the data back out. In the end, it looks like I gave up on optionbuttons in
W07 (that was the only project where I had tried in earnest to make them
work) and used checkboxes instead. I now recall reading that some folks used
VBA to fake the optionbutton group behavior using checkboxes, but I couldn't
because I can't guarantee my internal users will enable VBA.

It is a pity that the developers didn't see the value of continued support
of optionbuttons. I'm reading the Excel 2010 blogs at
http://blogs.msdn.com/excel/archive/tags/Excel+2010/default.aspx, and there
are a lot of good comments and information about Excel 2010 features. I
haven't looked for a similar blog on Word 2010, but if anyone has a link to
it, that would be a great place to ask about future support of this feature.

I may be misreading your reply, but I have a hazy recollection about whether
office supports object collection- if you can't find the collection, you'll
probably have to loop each optionbutton (if you can figure out a way to
access them- you probably know more about OLE objects than I do) and use your
own logic to determine which groups each button is in. I seem to recall a
project in Excel where I had to name my optionbuttons logically: opt1A,
opt1B, etc then cycle through all of them and apply Select Case
Instr(control.name,4,1) to give the group ID, then check each one's value to
see which was selected.

Wish I had a more useful answer to your problem.

Best,
Keith

 

[ker_01]

This seems to work- all credit goes to Greg Maxey

Adapt as needed.

Sub ReadValue()
Dim oILS As InlineShape
Dim oCtr As Object
For Each oILS In ThisDocument.Range.InlineShapes
If oILS.OLEFormat.Object.Name = "OptionButton1" Then
Set oCtr = oILS.OLEFormat.Object
Debug.Print oCtr.Value
End If
Next oILS
End Sub