Change checkbox values on Word Doc from multiple userforms

[Chunk]

Happy New Year,

Here is what I am attempting to do:

I have a main userform that the user adds some basic information to and also selects documents they need printed

There are 3 different types of materials documents (MS Word) they can print. Any variation can be selected (none also). I use the find and replace method to insert the data.

Depending on the type they select, more information is required of the user.

I have another userform activated to get that information from them (in the form of checkboxes).

What I am having problems with is getting the information from the second (sub) userform to the Word document.

Here is the code from the main userform:




Dim wrdApp As Object
Dim wrdNNDF As Object

Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("C:\Materials\MATChecklist.docm"



If ob_Mat1_Y.Value = True Or ob_Mat2_Y.Value = True Or ob_Mat3_Y.Value = True Then

UF_MatStatSht.Show



Here is the code from the second userform:



If optbut_Order_Y.Value = True Then
.cb_MAT_List.Value = True
End If

If optbut_Order_N.Value = True Then
.cb_MAT_NA.Value = True
End If

If optbut_Trigger_Y.Value = True Then
.cb_MAT_TRIGGER_Y.Value = True
End If

If optbut_Trigger_N.Value = True Then
.cb_MAT_TRIGGER_N.Value = True
End If

(the optbut objects are on the second userform; the ".cb" objects are on the word document "MATChecklist.docm")

If there is any other information you need, please let me know.

Thank you in advance,

Chunk

[gmayor]

From the small code clips it is difficult to imagine what you are trying to do, but the problem presumably relates to the check boxes in the document itself. What kind of check boxes are these? Content controls? Form fields? ActiveX check boxes? Each requires a different approach.

[Chunk]

Gmayor,

Sorry about that. I was trying to catch all the details….guess I missed that mark.

The checkboxes on the Word doc are ActiveX.

Here is the process that I am trying to achieve, followed by a better copy of the code I am using:

User makes selection of what Material Sheet they want printed (Mat1, Mat2, Mat3) via option button on the main userform; a non-selection is also an option

User selects “Print” button on main userform

Here is the code for the main userform:



Public Sub FindAndReplace_MaterialsChecklist()

Dim wrdApp As Object
Dim wrdNNDF As Object
Dim FindWhat, ReplaceWith
Dim FindWhat1, ReplaceWith1
Dim i As Integer
Dim j As Integer

Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("C:\Materials\MATChecklist.docm)
FindWhat = Array("XXX", "YYY", "ZZZ", "WWW")
ReplaceWith = Array(tbox_Title.Text, tbox_JO1.Text, tbox_JO2.Text, tbox_PackNum.Text)
FindWhat1 = Array("AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG", "HHH", "III", "JJJ", "KKK", "LLL", "MMM", "NNN", "PPP")
ReplaceWith1 = Array(tbox_Ref1.Text, tbox_Ref2.Text, tbox_Ref3.Text, tbox_Ref4.Text, tbox_Ref5.Text, tbox_Ref6.Text, tbox_Ref7.Text, tbox_Ref8.Text, tbox_Ref9.Text, tbox_Ref10.Text, tbox_Ref11.Text, tbox_Ref12.Text, tbox_Ref13.Text, tbox_Ref14.Text, tbox_Ref15.Text)
wrdNNDF.Activate
wrdApp.Selection.Find.ClearFormatting
wrdApp.Selection.Find.Replacement.ClearFormatting

For i = 0 To 3
With wrdApp.Selection.Find
.Text = FindWhat(i)
.Replacement.Text = ReplaceWith(i)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
Next i

For j = 0 To 14
With wrdApp.Selection.Find
.Text = FindWhat1(j)
.Replacement.Text = ReplaceWith1(j)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll
Next j

With wrdNNDF
.Activate

If ob_Mat1_Y.Value = True Or ob_Mat2_Y.Value = True Or ob_Mat3_Y.Value = True Then

UF_MatStatSht.Show

End if

wrdNNDF.PrintOut
wrdNNDF.Close SaveChanges:=wdDoNotSaveChanges
End With
wrdApp.Quit
End Sub



The user then makes option button selections on the second userform "UF_MatStatSht" and clicks the Continue button



Public Sub btn_Continue_Click()

If optbut_Order_Y.Value = True Then
.cb_DOC_List.Value = True
End If

If optbut_Order_N.Value = True Then
.cb_DOC_NA.Value = True
End If

If optbut_Trigger_Y.Value = True Then
.cb_DOC_TRIGGER_Y.Value = True
End If

If optbut_Trigger_N.Value = True Then
.cb_DOC_TRIGGER_N.Value = True
End If

End Sub



the ".cb........." objects are on the ActiveX checkboxes in the Word document, opened from the main userform.

I want the user selected options from the main and second userform to transfer to the Word doc, print, then close the doc without changes.

I hope that is better. Please let me know if you require more info.

Thanks again.

Chunk

[gmayor]

You can't access the check box directly by name. You need something like the following


Dim oShape As Object

If optbut_Order_N.Value = True Then
For Each oShape In wrdNNDF.InlineShapes
If oShape.OLEFormat.Object.Name = "cb_DOC_NA" Then
oShape.OLEFormat.Object.Value = True
Exit For
End If
Next oShape
End If

[Chunk]

Using the above code (added to the second userform) I am receiving the following error:

Runtime error '424'

Object required

Does it have anything to do with the document opening from the main userform?

[gmayor]

Although you posted in a Word forum, it seems that you are calling this macro from another Office application (otherwise there is no need to create wrdApp as you would already be running the code from Word).

The error probably has more to do with the macro not knowing what 'wrdNNDF' refers to. You can declare what it refers to in the macro as below. This should get the open instance of Word and 'opening' a document that is already open should simply give focus to that document.


Dim wrdApp As Object
Dim wrdNNDF As Object
Set wrdApp = GetObject(, "Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("C:\Materials\MATChecklist.docm")

[Chunk]

gmayor,

Again, my lack of details.

You are correct. The userforms are created and opened through Excel. (The reason I posted here was because of the document issue)

PROBLEM SOLVED

I added the above code to my second form and it worked like a snap.

A HUGE THANKS to gmayor for his help. (Once I gave him all the correct details :thumb)

Here is a copy of the code I used (the only thing that I changed was on the second userform):



Public Sub btn_Continue_Click()
Dim wrdApp As Object
Dim wrdNNDF As Object
Set wrdApp = GetObject(, "Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("C:\Materials\MATChecklist.docm)
Dim oShape As Object

wrdApp.Visible = False

If optbut_Order_Y.Value = True Then
For Each oShape In wrdNNDF.InlineShapes
If oShape.OLEFormat.Object.Name = "cb_DOC_List" Then
oShape.OLEFormat.Object.Value = True
Exit For
End If
Next oShape
End If

If optbut_Order_N.Value = True Then
For Each oShape In wrdNNDF.InlineShapes
If oShape.OLEFormat.Object.Name = "cb_DOC_NA" Then
oShape.OLEFormat.Object.Value = True
Exit For
End If
Next oShape
End If

If optbut_Trigger_Y.Value = True Then
For Each oShape In wrdNNDF.InlineShapes
If oShape.OLEFormat.Object.Name = "cb_DOC_TRIGGER_Y" Then
oShape.OLEFormat.Object.Value = True
Exit For
End If
Next oShape
End If

If optbut_Trigger_N.Value = True Then
For Each oShape In wrdNNDF.InlineShapes
If oShape.OLEFormat.Object.Name = "cb_DOC_TRIGGER_N" Then
oShape.OLEFormat.Object.Value = True
Exit For
End If
Next oShape
End If

Unload Me

End Sub




I am sure there is a more efficient way to do this.....but for now, it works (more studying to do).

Again, thanks gmayor for the help.

Chunk