Change value of a MS Word ActiveX Checkbox

[Chunk]

Scenario: I have a userform, created from VBA (in excel), that has multiple checkboxes that the user will select to print needed/wanted word documents. One of the documents is a check-off form (that I am not allowed to change the format of) that is basically a duplicate of the userform.

I am attempting to have the userform, when the button is selected, open the document and select the appropriate checkbox (in the document).

I have tried reading different forums to find where my trouble lies and am at a loss. I am not completely new to programming but would still consider myself a beginner.

I am currently receiving "Run-time error '5941': The requested member of the collection does not exist"

Here is the code for the userform:


Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then
CheckTheCheckbox
End If
End Sub

Public Sub CheckTheCheckbox()
Dim wrdApp As Object
Dim wrdNNDF As Object
Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("J:\TWDChecklist.docm")
wrdNNDF.Activate
wrdApp.Visible = True
If CheckBox2.Value = True Then
ActiveDocument.FormFields("cb_DOC_WAF_Y").CheckBox.Value = True
End If
End Sub

Any help is greatly appreciated as I am starting to get a bruise on my forehead from banging it against the desk for the past week. Please let me know if there is more info that is required.

Sincerely,

Chunk

[Aussiebear]

Are the checkboxes correctly named?

[Chunk]

I triple checked the checkbox names on the userform and the document.... they are correct

[ZVI]

Try this:
wrdApp.ActiveDocument.FormFields("cb_DOC_WAF_Y").CheckBox.Value = True

[Chunk]

ZVI,

I am still receiving the "Run-time error '5941': The requested member of the collection does not exist" error.

[Chunk]

I am using VBA 7.0 and MS Office 2010 (Excel and Word)

[Aussiebear]

There's something about the structure of the code here that worries me. Your naming of checkboxes runs contrary to each other.

In Excel, you have Checkbox1.value, but implied in word you have both Checkbox2.value and Activedocument.formfields.("cb_DOC_WAF_Y").Checkbox.value.. The naming is not consistent. Hence my initial question are the checkboxes named correctly. Is checkbox2 definitely in the word document?

[snb]

Sub CheckTheCheckbox()
with getobject("J:\TWDChecklist.docm")
.windows(1).Visible = True
for each it in .shapes
if it.OLEFormat.Object.Name="cb_DOC_WAF_Y" then it.OLEFormat.Object.Value=true
next
for each it in .inlineshapes
if it.OLEFormat.Object.Name="cb_DOC_WAF_Y" then it.OLEFormat.Object.Value=true
next
End With
End Sub

[Chunk]

Aussiebear,

I see where the question came from, after looking a little closer. Hopefully this explanation will help clear it up (I hope). I have a userform with 2 checkboxes and a button. "Checkbox 1" and "Checkbox 2" respectively. The checkbox on the word doc is "cb_DOC_WAF_Y". Hope that helped.

[snb]

You didn't specify which kind of checkbox: a formfield or an ActiveX_control.

[Aussiebear]

Okay, then to my reading of the code


wrdNNDF.Activate
wrdApp.Visible = True
If CheckBox2.Value = True Then
ActiveDocument.FormFields("cb_DOC_WAF_Y").CheckBox.Value = True

The active object here is word but the checkbox 2 is on a user form in Excel.

[ZVI]

Please run the bellow debugging code and let us know what is printed in the Immediate window


Sub Test_CheckTheCheckbox()
' Debugging subroutine

Const wdFile As String = "J:\TWDChecklist.docm" '<-- change to suit

Const wdFieldFormCheckBox As Long = 71
Dim wdApp As Object, wdNNDF As Object, wdFormField As Object
Dim i As Long
Dim s As String

' Get/Create Word.Application
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
End If
On Error GoTo 0

' Open document with Form Checkboxes
Set wdNNDF = wdApp.Documents.Open(wdFile, ReadOnly:=True)

' Print to the Immediate window the name and value of each Form Checkbox
Debug.Print wdNNDF.FullName
For Each wdFormField In wdNNDF.FormFields
If wdFormField.Type = wdFieldFormCheckBox Then
i = i + 1
s = s & wdFormField.Name & vbLf
Debug.Print i, wdFormField.Name, wdFormField.CheckBox.Value
End If
Next

' Return back to Excel
AppActivate Application.Caption

' Show result
If i = 0 Then
Debug.Print "Form Checkboxes not found"
MsgBox "There is no Form Checkboxes in word document"
Else
MsgBox "Form CheckBoxes found : " & i & vbLf & s
End If

' Release the memory of object variables
Set wdNNDF = Nothing
Set wdApp = Nothing

End Sub

[Chunk]

Sorry about the delay...

ZVI: I ran the debugging code and received "There is no Form Checkboxes in word document".

Aussiebear: I want the user to select applicable checkboxs on the userform (CheckBox2). When a button on the userform is clicked it opens the word document and makes a checkbox on the word document the same value as the checkbox on the userform.

snb: ActiveX

[Chunk]

SOLVED on thread 52044