Solved: Get MS Word OptionButton value from Excel VBA

[rico99]

Hi all,

I've create a MS Word document which contain an OptionButton, i want to retrieve the value (checked or unchecked) from an Excel macro.

The goal is to collect answers from many filled word documents, and put it into an Excel Spreadsheet.

Many thanks for your help

[Kenneth Hobs]

What kind of option button, the wdFieldFormCheckBox? Maybe you could attach a short example DOC.

[rico99]

[IMG]file:///C:/DOCUME%7E1/ERIC%7E1.MAL/LOCALS%7E1/Temp/moz-screenshot-1.jpg[/IMG]Hi Kenneth,

It's not a form, i've put the screenshot of toolbar and the composant for which i want to retrieve value from Excel macro ... hope you can see it, it will be a bit complicate to upload it

Many thanks for your help

[Kenneth Hobs]

Good example. It is from the Control Toolbox toolbar I see. I like those better.

Since you can't post a sample doc, I will create a simple one to test. I will look into the matter later today and post back.

[rico99]

Many thanks Kenneth

[Kenneth Hobs]

I used both early and late binding so you will need to set the MSWord reference. You will need to change the Inputs wordFilename and startColumnName to fit your needs.

[vba]
Sub MSWordOptionButtionInfo()
'Requires reference: MSWord 11.0 Object Library
Dim oShape As Word.InlineShape
Dim wdApp As Object, wd As Object, rn As Long
Dim wordFilename As String, startColumnName As String
Dim r As Range, counter As Integer

'Inputs
wordFilename = "x:\MSWord\OptionButtons.doc"
startColumnName = "A"

'Exit if word file does not exist
If Dir(wordFilename) = "" Then Exit Sub

'set wdApp reference
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'Set DOC file with option buttons to open
Set wd = wdApp.Documents.Open(wordFilename)
wdApp.Visible = False

'Set Initial Range and counter
Set r = Range(startColumnName & Rows.count).End(xlUp).Offset(1, 0)
counter = 0

'Put option button name at in of startColumnName and value in cell to the right
For Each oShape In ActiveDocument.InlineShapes
If oShape.OLEFormat.progID = "Forms.OptionButton.1" Then
r.Offset(counter, 0).Value = oShape.OLEFormat.Object.Name
r.Offset(counter, 1).Value = oShape.OLEFormat.Object.Value
counter = counter + 1
End If
Next oShape

Set wd = Nothing
Set wdApp = Nothing
End Sub[/vba]

[rico99]

Hi Kenneth,

it's working wonderfully...you're a king !!!

many thanks to you

Cheers

[spalmgren]

Yeah that's correct, I am using 2007. Do you know what could have changed?

From the debug it seems like it is the "oShape" causing the problem.

[Kenneth Hobs]

I won't get 2007 for a few weeks so I can't test. I will get 2010 in a day or so. Until then, you could record a macro in MSWord where you add the control (option button) and post it here. I can then see what was changed possibly.

[spalmgren]

In 2007 it looks like this when I add an ActiveX Option button.
[VBA]Sub AddButton()
'
' AddButton Macro
'
'
ActiveDocument.ToggleFormsDesign
Selection.InlineShapes.AddOLEControl ClassType:="Forms.OptionButton.1"
End Sub[/VBA]

[Kenneth Hobs]

It appears to be the same. At this point, you might post a sample DOCX. I would have to convert it to a DOC to test. I don't expect to find a problem though.

[spalmgren]

Here is a sample .doc file.

[Bob Phillips]

If Word is like Excel, the macro recorder doesn't do much in 2007.

[GTO]

If Word is like Excel, the macro recorder doesn't do much in 2007.

Golly, just when I thought ribbons should be on Christmas/Birthday presents, another selling point...