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 )

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