Need to extract *formfield* optionbutton value via VBA

[ker_01]

First, ARGGGHHH I hate the buggy MS newsgroup web interface (my only option,
since NNTP is blocked by our firewall). Here goes attempt #3 to post my
question, since it crashed on my last two attempts.

I have added optionbuttons on a Word2007 form using Graham Mayor's code:
groups.google.com/group/microsoft.public.word.docmanagement/browse_thread/thread/84e55b879c2da74e/e07cd930a48e9f75?lnk=raot
(FWIW, I can't even find this newgroup on the MS web interface, otherwise I
would have just posted a followup in that thread)

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

Now I have optionbuttons in my document, and I need to be able to check
their values based on the control name, such as "opt1Y1".

Debug.Print wrdDoc.FormFields("opt1Y1").OptionButton.Value doesn't work;
auto-complete doesn't show OptionButton as a valid property

Debug.Print wrdDoc.InlineShapes("opt1Y1").Value doesn't work, I think I'm
missing part of the syntax

Can anyone tell me how to check the value of a specific named optionbutton
that has been added to a Word2007 document using Graham's code above?

Thank you!!!
Keith

 

[Gordon Bentley-Mix]

Keith,

Unfortunately, I don't have the time to check but have you tried looking at
the .Result property for the FormField? If memory serves, it should be True
if the option button is selected and False if it's not.

(And my sympathies for the challenges of the web interface. Frustrating
as... ;-P)
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix on news.microsoft.com]

Keith,

My apologies for possibly leading you astray. Now that I've had a chance to
dig a bit deeper, it appears that my recommended approach will not work as
Option Button controls are definitely NOT formfields. Unfortunately, I cannot
figure out what they are in terms of the Word OM. Accordingly, trying to
evaluate the .Result property won't work. Since I almost never work with
controls of this type, I'm afraid I don't have any further suggestions.
Perhaps Graham will know.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Greg Maxey]

Keith,

If you know the specific index number in the InlineShape collection
then you can use:

Sub ScratchMacroI()
Dim oCtr As Object
Set oCtr = ThisDocument.Range.InlineShapes(1).OLEFormat.Object
Debug.Print oCtr.Value
Set oCtr = Nothing
End Sub

Otherwise you would need to (or at least I think you would need to)
loop through each control until you find the specified name and then
get the value:

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

 

[Doug Robbins - Word MVP]

This is a response that I provided to another person who wanted to get
values based on which Option Button was clicked:

From: "Doug Robbins - Word MVP" <[email protected]>
Subject: Re: Calculating option buttons on a Word 2007 form
Date: Wednesday, 17 June 2009 6:55 PM

Put a TextInput FormField at the end of each question and use the following
code in the ThisDocument object for the document in the Visual Basic Editor

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
ActiveDocument.FormFields("Question1").Result = 1
End If
ActiveDocument.Fields.Update
End Sub
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
ActiveDocument.FormFields("Question1").Result = 2
End If
ActiveDocument.Fields.Update
End Sub
Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
ActiveDocument.FormFields("Question1").Result = 3
End If
ActiveDocument.Fields.Update
End Sub
Private Sub OptionButton4_Click()
If OptionButton4.Value = True Then
ActiveDocument.FormFields("Question1").Result = 4
End If
ActiveDocument.Fields.Update
End Sub
Private Sub OptionButton5_Click()
If OptionButton5.Value = True Then
ActiveDocument.FormFields("Question1").Result = 5
End If
ActiveDocument.Fields.Update
End Sub

That will cause the number corresponding to the OptionButton that has its
value set to True to be displayed in the formfield "Question1" Do the same
thing for your other questions

Finally, have a TextInput Formfield that you set to a Calculation type and
in which you set the Expression to be = Question1 + Question2 + Question3 +
etc. to hold the result.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

I posted this to the wrong discussion group earlier....I'm using Word 2007
to create an on-line locked form. There are several questions on the form.
Each question has 5 answers to choose from, for which I'm using Option
Buttons. I want OptionButton 1's value to =1, OptionButton2=2,
OptionButton3=3, etc. At the end of each question, I’d like to display
the
value of the option button chosen. Then, at the very end, I'd like to use
a
text box where I would like to display the total value of all the
OptionButtons selected. (ie. If there are 5 questions, Option button 4
chosen for each question; the final text box would display 20). Can this
be
done? If so, how do I begin?

Thanks in advance!
Tina

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[ker_01]

Thank you to both Gordon and Doug for your responses.

The form will be widely disseminated, and I can't guarantee that users will
enable macros, so I'll need to ensure that the form provides for the
subsequent data scraping under all user conditions. There actually isn't any
other VBA in the form itself (other than temporarily, when I added the
optionbuttons via VBA). The VBA code will all be in a separate document that
is designed to scrape the contents of the multitude (err... ok, maybe 20 per
day) forms that are returned.

Another option would be to might push the values into custom document
properties, just to avoid adding anything to an already overly complicated
form...but, I can't think of any way to do that if the end users aren't
enabling macros.

Doug, I'm assuming based on your response that there may not be a way (other
than using the 'click' event) to retrieve the value of the optionbutton-
which means if I can't use VBA, I may have to go back to independent
checkboxes, and just build in extra layers of data validation to identify any
instances where multiple (mutually exclusive) options are checked.

I know there is code to make checkboxes act mutually exclusive, but I
believe that all relies on VBA as well.

If there are any other creative ways to get the value of the control
directly, please let me know- I'm willing to try anything!

Thank you,
Keith

 

[Doug Robbins - Word MVP]

You could consider using dropdown formfields in place of checkboxes.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Cindy M.]

Hi Ker,

The form will be widely disseminated, and I can't guarantee that users will
enable macros, so I'll need to ensure that the form provides for the
subsequent data scraping under all user conditions. There actually isn't any
other VBA in the form itself (other than temporarily, when I added the
optionbuttons via VBA). The VBA code will all be in a separate document that
is designed to scrape the contents of the multitude (err... ok, maybe 20 per
day) forms that are returned.

Which version(s) of Word are you targeting? Unless you can use the 2007 file
format, you're pretty much up the proverbial creek with this... If VBA isn't
enabled, the ActiveX option buttons won't work.

It is possible to link a DocProperty field to a document property, but it won't
update to that property until the next time the document is opened.

Beyond that, about the only thing you can use is the (highly volatile) bookmark
feature

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[ker_01]

99% of our users should be on 2007. Our organization officially upgraded, and
there are only a few 2003 holdouts (including me; my main machine is still
2003 due to the poor Excel2007 VBA performance- but I have a 2007 machine as
well).

I'm probably making all types of bad assumptions; I had thought that the OLE
object was functioning as a formfield object instead of activeX (or is that
what OLE means?). When I right-click on the optionbutton, the right-click
menu includes Optionbutton Object-> Convert, which then indicates current
type as MS Forms 2.0 optionbutton, and the only option is to convert it to a
MS Forms 2.0 optionbutton

I may also be making a poor assumption about how 2007 VBA security works; is
it possible to implement any of the suggestions in this thread without the
user actually having to respond to the "Security Warning: some active content
has been disabled" message in the message bar? If so, then I'd be happy to
pursue that option. The challenge for me is that (past experience) I'm going
to have a lot of users who just won't enable macros, if that is a user-driven
choice.

Best,
Keith

 

[ker_01]

I hadn't played with "link to content" in the document properties
before-that's kind of cool- but while the checkboxes, text fields, and
drop-down fields show up, the optionbuttons don't appear in the list for
linking.

It would be ok with me if the document property was only updated when the
file is opened, as it will be opened at the central location to scrape the
contents, and that is when the "current" values would be needed.

Thank to all for your continued advice and direction, I appreciate your
expertise!

Keith

 

[Cindy M.]

Hi Keith,

99% of our users should be on 2007

OK, this does open up some perspectives...

I had thought that the OLE
object was functioning as a formfield object instead of activeX (or is that
what OLE means?)

Yes, OLE = ActiveX; formfields are something else. When you insert a Forms 2.0
control (ActiveX), that automatically activates a stub of VBA. So just doing
that is going to activate macro security. However...

is
it possible to implement any of the suggestions in this thread without the
user actually having to respond to the "Security Warning: some active content
has been disabled" message in the message bar

This is going to depend a bit on your Office configuration and how
"sophisticated" your users OR IT department are. But 2007 is different from
earlier versions.

Go to the "Word Options", then the "Trust Center" and click the button to go to
the settings. As you'll see in "Macro Settings", by default, macros are going to
be disabled, which is not a bad thing. Now click on the "Trusted Locations".
Here, a user can set a folder as a "trusted location": documents with macros
stored in such a folder are allowed to run. The IT department should be able to
add locations via a Group Policy (you should consult the ORK and maybe the
office.setup newsgroup if you need help with this).

The other possibility is the Trusted Publishers tab. This would be an option if
your company has a digital code-signing certificate. For in-house use, this
could be an internal certificate, generated by Windows Server; for external use
you'd have to have a certificate issued by a company such as Verisign, Thawte or
GlobalSign. Here, again, you can use a Group Policy to "push" the trusted
publisher to the users' machines. (Or, they can click a button in a message the
first time they open a digitally signed document in order to trust the
publisher.)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Cindy M.]

Hi Keith

I hadn't played with "link to content" in the document properties
before-that's kind of cool- but while the checkboxes, text fields, and
drop-down fields show up, the optionbuttons don't appear in the list for
linking.

No, you'd have to use something other than an options button - a checkbox,
perhaps.

The other possibility would be to use a Macrobutton or formfield checkboxes
- but have the code external to the document, in a global template (loaded
at Startup). Code in templates in the Startup folder are automatically
trusted (see my other reply).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail