PDA

View Full Version : [SOLVED] Change value of a MS Word ActiveX Checkbox



Chunk
03-16-2015, 07:50 AM
Hello all,
I am using VBA 7.0 and MS Office 2010 (Excel and Word)
What I have is a userform with
1 checkbox: "cbox_one"
and a button: "btn_one"
I also have 2 documents: "Checklist" and "Procedure"
The "Checklist" document is esentially a copy of the userform. It also has an ActiveX Control checkbox labeled "cbox_doc_one"

I can open and print the documents just fine. I am receiving "Run-time error '5941': The requested member of the collection does not exist"
when I run the following code:

#Private Sub btn_one_Click()
If cbox_one.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:\Checklist")
wrdNNDF.Activate
wrdApp.Visible = True
If cbox_one.Value = True Then
ActiveDocument.FormFields("cbox_doc_one").CheckBox.Value = True
End If
End Sub#

What I want to happen is this:
The user selects the checkbox on the userform then clicks the button.
"Procedure" is then printed.
"Checklist" is opened, "cbox_doc_one" is set to true.
"Checklist" is printed, closed without saving.
Any ideas where I went awry? Thanks in advance.
Chunk

PS Started on a previous thread (I don't know how to link it... Same title)

Kenneth Hobs
03-16-2015, 09:53 AM
Where is the file extension?

Set wrdNNDF = wrdApp.Documents.Open("J:\Checklist")

Please use code tags. You can click the # icon to insert code tags.

When referencing another link, just copy and paste the URL.

Chunk
03-16-2015, 11:11 AM
Thanks Kenneth,

I knew there was a way to do that, just couldn't find it. The part I can't get is changing the value of the checkbox on the word document. Any help is greatly appreciated.



Private Sub btn_one_Click()
If cbox_one.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:\Checklist.docm")
wrdNNDF.Activate
wrdApp.Visible = True
If cbox_one.Value = True Then
ActiveDocument.FormFields("cbox_doc_one").CheckBox.Value = True
End If
End Sub


Keep getting this "Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words." when trying to post the link.

Kenneth Hobs
03-16-2015, 11:54 AM
Are you sure it is an ActiveX control and not a Content Control? If you like, delete all but the control and attach the MSWord file. It is the best way to get help sometimes.

When posting a link, if you have problems, just say, see link 52044 (this thread) for example.

Some of these threads might help.

'Automate MSWord from another VBA Application like Excel' http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=169:automate-microsoft-word-from-excel-using-vba&catid=79&Itemid=475


'TypeText method
' http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
' http://www.excelforum.com/showthread.php?p=1946784
' http://vbaexpress.com/forum/showthread.php?p=169877
' http://vbaexpress.com/forum/showthread.php?t=24693
' http://www.excelforum.com/excel-programming/791302-excel-to-word-paragraph-and-page-setup.html
' http://www.vbaexpress.com/forum/showthread.php?50947


'Copy from Excel, paste to Word
'Lucas, http://vbaexpress.com/forum/showthread.php?p=178364
'http://www.vbaexpress.com/forum/showthread.php?50947


'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200
' http://www.excelforum.com/excel-programming/799070-import-text-fields-from-word.html
' Content Controls
' http://www.vbaexpress.com/forum/showthread.php?t=39654


'Add Hyperlink to Bookmark
' http://www.excelforum.com/excel-programming/664078-use-excel-vba-to-add-a-hyperlink-to-a-word-document.html#post2006430
'Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054


'Save OLEObject as MSWord Document
' http://vbaexpress.com/forum/showthread.php?t=21619
' http://vbaexpress.com/forum/showthread.php?t=24292
' http://www.excelforum.com/excel-programming-vba-macros/940687-excel-export-to-ole-object-with-user-prompted-saveas-help.html?p=3336342


'Add Table to MSWord
' http://vbaexpress.com/forum/showthread.php?t=23975
' http://vbaexpress.com/forum/showthread.php?p=168731
' http://www.vbaexpress.com/forum/showthread.php?48227-Macro-to-copy-multiple-tables-in-a-single-sheet-from-excel-to-word-doc


'Import Word Tables
'vog, http://www.mrexcel.com/forum/showthread.php?t=382541
'Ruddles, http://www.mrexcel.com/forum/showthread.php?t=524091


'snb, Word Tables
' http://www.vbaexpress.com/forum/showthread.php?t=45520
' http://www.vbaexpress.com/forum/showthread.php?t=46472




'Get Optionbutton info from MSWord DOC
' http://vbaexpress.com/forum/showthread.php?t=22454


'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.vbaexpress.com/forum/showthread.php?p=250215
' http://www.vbaexpress.com/forum/showthread.php?t=42833
' http://support.microsoft.com/kb/240157
' http://word.tips.net/T001833_Generating_a_Count_of_Word_Occurrences.html


' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html


'Bookmarks
' http://vbaexpress.com/forum/showthread.php?p=185718
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054
' http://www.vbaexpress.com/forum/showthread.php?p=253277


'Mail Merge
' http://www.excelforum.com/excel-programming/796614-mail-merge-from-excel.html
' http://www.excelforum.com/excel-programming/798299-print-mail-merge-document.html
'Word 's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). _
To see how to group records with any mailmerge data source supported by Word, _
check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
' http://lounge.windowssecrets.com/index.php?showtopic=731107
' or
' http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
' Mail Merge from Excel to MSWord: ElephantsRus
' https://app.box.com/s/0zlydxinl10t23mifkrr


' Control Word from Excel
' http://word.mvps.org/faqs/InterDev/ControlWordFromXL.htm
' http://word.mvps.org/FAQs/InterDev/MakeAppInvisible.htm

Chunk
03-17-2015, 04:44 AM
Kenneth,

Here is an example of the doc and the userform I am using.

Hope this sheds some light to my lacking description.

1302313024

Thanks for the links, I will be checking them out.

I "think" the previous thread is 51770.

Thanks in advance.

Chunk (aka Brian)

Kenneth Hobs
03-17-2015, 06:35 AM
Set the MSWord reference as I commented. This lets you use intellisense for that object since I used early binding.

Change the value for doc to point to your file.

Add this code to your userform. Note that the debug.print shows the results for the two controls in the Immediate Window of the VBE that you can view when it completes.

Private Sub CommandButton1_Click() CheckBox1Check
CheckBox1.Value = False
Unload Me
End Sub


Private Sub CheckBox1Check()
'Requires Tools > References > Microsoft Word 15.0 Object Library, or your version of MSWord
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim doc As String


doc = "X:\FileReadWrite\Test\ExampleDoc.docm"
If Dir(doc) = "" Then
MsgBox "Error, file does not exist." & vbLf & doc, vbCritical, "File is Missing"
Exit Sub
End If


On Error GoTo errorHandler


Set wdApp = New Word.Application


With wdApp
'Add makes a copy like from a template even though it may be a DOC file.
'Set wdDoc = .Documents.Add(Template:=doc)
Set wdDoc = .Documents.Open(doc)
'.Visible = True
End With


With wdDoc
Debug.Print "CheckBox1.Value: " & CheckBox1.Value, ".cbox_Doc_one.Value: " & .cbox_Doc_one.Value
If CheckBox1.Value = True Then
.cbox_Doc_one.Value = True
'Print doc
'wdApp.PrintOut Filename:=doc, Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
Else
.cbox_Doc_one.Value = False
End If
.Close False
End With


errorExit:
Set wdDoc = Nothing
Set wdApp = Nothing

Exit Sub


errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit
End Sub

Chunk
03-19-2015, 07:14 AM
Kenneth,

I thought I would be able to adapt the code you provided to fit my needs. Due to my lack of experience....I failed. I should have posted this to begin with and for that I am sorry. The document has multiple checkboxes (essentially each line is yes/no). Each row more or less coincides with a document the user needs printed. Here is a wiped version of the document and userform.



The user selects the document(s) they need printed on the userform then the print button is clicked. Depending on the selections from the userform, the applicable document(s) is printed. After those are printed, the checklist document is printed with the checkboxes mirroring the userform. I would like the userform to remain open after printing.

The other documents print fine (All that happens with them is a find and replace, then print).

With the code you provided The document flashes on the screen without printing. I attempted to add in the print code and received an "Unexpected code: 438" error Object doesn't support this property or method". I am also having an issue now with WINWORD.exe remaining open after all is said and done.

Chunk
03-20-2015, 05:07 AM
Many thanks to Kenneth,

Thanks for helping talk me through it. Here's what I ended up with, although it could probably be coded better:


Dim wrdApp As Object
Dim wrdNNDF As Object

Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("J:Checklist.docm")

wrdNNDF.Activate


If cb_1_Y.Value = True Then
With wrdNNDF
.cb_doc_1_Y.Value = True
End With
Else
With wrdNNDF
.cb_doc_1_N.Value = True
End With
End If

If cb_2_Y.Value = True Then
With wrdNNDF
.cb_doc_2_Y.Value = True
End With
Else
With wrdNNDF
.cb_doc_2_N.Value = True
End With
End If
wrdNNDF.PrintOut
wrdNNDF.Close SaveChanges:=wdDoNotSaveChanges
wrdApp.Quit

Pretty much rinse and repeat for each set of yes/no checkboxes.
Again, thank you for the help.
Chunk

Kenneth Hobs
03-20-2015, 06:26 AM
Good deal.

My spin on multiple if's like that is to make a Subroutine to call and pass inputs or to poke the objects into an array and iterate that. For just 3 cases, as you did it is fine.

Use With like this below. The purpose is to reduce the number of calls to the object. It just speeds it up a bit. You might save a whole microsecond this way.


Sub ken() Dim wrdApp As Object
Dim wrdNNDF As Object


Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("J:Checklist.docm")

With wrdNNDF
.Activate

If cb_1_Y.Value = True Then
.cb_doc_1_Y.Value = True
Else
.cb_doc_1_N.Value = True
End If

If cb_2_Y.Value = True Then
.cb_doc_2_Y.Value = True
Else
.cb_doc_2_N.Value = True
End If

.PrintOut
.Close SaveChanges:=wdDoNotSaveChanges
End With

wrdApp.Quit
End Sub

Chunk
03-21-2015, 06:58 AM
Thank you again sir
That will help as I have approximately 60 checks to do.
That would also explain the slowness I am currently experiencing.

Kenneth Hobs
03-21-2015, 07:34 AM
If you have that many, I would suggest doing a bit more work to make it easier to maintain the code.

One can use OleObjects() and concatenate the numerical string part of the control names if you have a 1 to 1 correlation among the controls. If not, this untested array approach would be worth doing. Of course there is noting wrong with 60 IFs if you want to go that route.


Sub ken()
Dim wrdApp As Object
Dim wrdNNDF As Object
Dim cb(1 To 3, 1 To 2) As Object, i As Integer

Set wrdApp = CreateObject("Word.Application")
Set wrdNNDF = wrdApp.Documents.Open("J:Checklist.docm")

With wrdNNDF
.Activate

Set cb(1, 1) = cb_1.Y
Set cb(2, 1) = .cb_Doc_1_Y
Set cb(3, 1) = .cb_Doc_1_N
Set cd(1, 2)= cb_2.Y
Set cb(2, 2) = .cb_Doc_2_Y
Set cb(3, 2) = .cb_Doc_2_N

For i = 1 To UBound(cb, 2)
If cb(1, i).Value = True Then
cb(2, i).Value = True
Else
cb(3, i).Value = True
End If
Next i

.PrintOut
.Close SaveChanges:=wdDoNotSaveChanges
End With

wrdApp.Quit
End Sub