MS Word VBA Retrieve Option Group Values

[mfleming]

Hello.

I'm trying to create a bunch of questions using ActiveX Option Buttons and Groups. I then want to retrieve values and calculate the sum of these values.
I'm not sure how to code any of this in Microsoft Word 2003/2010?
These Option buttons are on the standard document page and not in a "UserForm"
See Attached.


Public SumVal As Integer

OPTION GROUP "G1"
Option Button 1 (If selected SET SumVal = SumVal + 4)
Option Button 2 (If selected SET SumVal = SumVal + 3)
Option Button 3 (If selected SET SumVal = SumVal + 2)
Option Button 4 (If selected SET SumVal = SumVal + 1)

OPTION GROUP "G2"
Option Button 5 (If selected SET SumVal = SumVal + 4)
Option Button 6 (If selected SET SumVal = SumVal + 3)
Option Button 7 (If selected SET SumVal = SumVal + 2)
Option Button 8 (If selected SET SumVal = SumVal + 1)

RESET (Changes all Option Buttons to "False")
CALCULATE (Retrieves the SumVal Results and places this value in a LABEL called lblCalcValues



Example.
Option Button 2 Selected
Option Button 5 Selected
SumVal = 7

See attached.

How do I code this?

[gmaxey]

You could save yourself lots of code by using a naming convention (e.g., G1A, G1B, G1C, G1D, G2A, etc)


Sub TallySelectedOptionBoxes()
Dim ctl As InlineShape
Dim i As Long
For Each ctl In ActiveDocument.InlineShapes
If ctl.Type = wdInlineShapeOLEControlObject Then
If TypeOf ctl.OLEFormat.Object Is MSForms.OptionButton Then
If ctl.OLEFormat.Object.Value = True Then
Select Case True
Case InStr(ctl.OLEFormat.Object.Name, "A") > 0
i = i + 4
Case InStr(ctl.OLEFormat.Object.Name, "B") > 0
i = i + 3
Case InStr(ctl.OLEFormat.Object.Name, "C") > 0
i = i + 2
Case InStr(ctl.OLEFormat.Object.Name, "D") > 0
i = i + 1
End Select
End If
End If
End If
Next
MsgBox i
End Sub

[mfleming]

I have a list of 60 options (15 groups).

Is there an easy way to do a one time rename OptionButton.Name to make it cleaner like you mentioned?

What's with "InlineShapes"? Is this what is used for all ActiveX Text box/Option Box/Combox box's with using a "Userform"?

[gmaxey]

Writing code to rename them would probably take as much or more time as it would take to do it one by one (for me at least).

"... with using a UserForm?"

I thought you were using ActiveX controls not a UserForm. Yes, for ActiveX controls it is "InlineShapes." That is what they are.

See: http://msdn.microsoft.com/en-us/library/aa140269(office.10).aspx

[mfleming]

Sorry about that.

Yea I'm using ActiveX. I wasn't sure if you use the calls for InlineShapes when using a UserForm (which I'm not using on this particular project).


The code I was thinking would be just a module activated by a button.

Basically use a While loop to go through the document and check for these InlineShapes and use a counter to rename them? Again there are a LOT of them so this would be good to know how to do (as well as in the future if possible)

[gmaxey]

The difficulty would be resetting the counter when you hit a new group name. You would use a loop like already shown and something like:


With ctl.OLEFormat.Object
If .GroupName = "G1" Then
.Name = "G1-" & i '(some counter)
i + i + 1
ElseIf .GroupName = "G2" Then
etc.

[mfleming]

k, I will try that.

Also how would I set a "label" or textbox to the variable "i"
Do I use MSForms.Label?

[gmaxey]

Did you read the linked article? Let's say your label is named "Label1"
In the ThisDocument class module:


Sub ScratchMacro()
Dim i As Long
i = 20
ThisDocument.Label1.Caption = i
End Sub

[mfleming]

I used the following code to Re-Name all my Option Buttons and set default values to False (only works a single time)



Sub ReNumberOptionBoxes()
Dim ctl As InlineShape
Dim i As Long
Dim iTot As Long
Dim iLet As String
Dim iNum As Long
Dim OptName
Dim OptFixed As String
'InlineShapes is used for ActiveX Controls
i = 1
iNum = 0
iLet = ChrW(iNum + 65)
iTot = 1
OptFixed = "G"
For Each ctl In ActiveDocument.InlineShapes
If ctl.Type = wdInlineShapeOLEControlObject Then
' Checks for Option Buttons in Document
If TypeOf ctl.OLEFormat.Object Is MSForms.OptionButton Then
OptName = OptFixed & i & iLet
ctl.OLEFormat.Object.Name = OptName
ctl.OLEFormat.Object.Value = False
If iTot = 4 Then
iTot = 0
iNum = 0
i = i + 1
iLet = ChrW(iNum + 65)
Else
iTot = iTot + 1
iNum = iNum + 1
iLet = ChrW(iNum + 65)
End If
End If
End If
Next
'MsgBox i
'Enters total select over Total Avaiable Values
'ThisDocument.lblCalcVal.Caption = i & "/" & iTot
End Sub




I Then used gmaxey code to tally up the results.

Worked Perfect!

Thanks!