Hi,
I’m still using Word 2010 for Word Automation and I need to change check boxes values while generating an automated report. I do use the guides “I Wish I knew how to…” which are very good. I did not find any instructions about how to deal with neither “Legacy” OLE Controls or Content controls.
I know the Class WordOleControl exists but I’d prefer use the newer Content Controls. Is there a class for them as well?
Thanks!
Hello Roger,
Yes checkboxes can have different values when generating a report. Here is an example that populates cell C2 with either the checkbox on or off.
Checkbox On
[code]Sub Action() Handles Action
Dim Excel as new ExcelApplication
Excel.Workbooks.Add
Excel.Visible = true
Dim MyLeft as Double = Excel.Cells(2, “C”).Left
Dim MyTop as Double = Excel.Cells(2, “C”).Top
Dim MyHeight as Double = Excel.Cells(2, “C”).Height
Dim MyWidth as Double =Excel.Cells(2, “C”).Width
Excel.ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select_
Excel.Selection.Caption = “My Caption”
Excel.Selection.Value = Office.xlOn //CheckBox ‘On’
End Sub
[/code]
Checkbox Off
[code]Sub Action() Handles Action
Dim Excel as new ExcelApplication
Excel.Workbooks.Add
Excel.Visible = true
Dim MyLeft as Double = Excel.Cells(2, “C”).Left
Dim MyTop as Double = Excel.Cells(2, “C”).Top
Dim MyHeight as Double = Excel.Cells(2, “C”).Height
Dim MyWidth as Double =Excel.Cells(2, “C”).Width
Excel.ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select_
Excel.Selection.Caption = “My Caption”
Excel.Selection.Value = Office.xlOff //Checkbox ‘Off’
End Sub
[/code]
The Checkbox is an example of a content control that can be selected in Xojo at runtime.
Does this work for you?
Here is how to make checkboxes with a caption in Word:
Checkbox is checked
[code]Dim Word as new WordApplication
Word.Documents.Add
Word.Visible = true
Word.ActiveDocument.FormFields.Add(Word.ActiveDocument.Range(0,0), Office.wdFieldFormCheckBox)
Word.ActiveDocument.FormFields(1).CheckBox.Value = True //Checkbox is checked
Word.Selection.Text = “My Caption”[/code]
Checkbox is not checked
[code]Dim Word as new WordApplication
Word.Documents.Add
Word.Visible = true
Word.ActiveDocument.FormFields.Add(Word.ActiveDocument.Range(0,0), Office.wdFieldFormCheckBox)
Word.ActiveDocument.FormFields(1).CheckBox.Value = False //Checkbox is not checked
Word.Selection.Text = “My Caption”
[/code]
Edit: Result is not needed - and was removed
Thanks a lot Eugene!
That should do it.
Since I use .dotx templates, controls are already in place. I have to modify their value according to database readings so I need to access each of them one by one to attribute them a value. I see they can be identified by “item value” but, is it possible to access a specific checkbox by using it’s bookmark value? It appears more functional to me then relying on an item number.
By the way, I appreciate a lot your books. They helped me a lot. If I may suggest, setup tips might be usefull to users who need to use Office automation as a service. I had to spent quite lots of time figuring out how to make it work in that context. I guess you don’t but if you eventually need references about that aspect, feel free to ask. It will be a pleasure to share what I found. As it happens most of the time, it is quite simple… when you know what to do…
Thanks again!
Roger
Hi Roger,
A bookmark just shows where you are in the document, and the name is the label for the control. I think you mean label, right? Here is an example that creates two checkboxes, and one has the CheckBox name First, and the other CheckBox name is Second.
[code]Dim Word as new WordApplication
Word.Documents.Add
Word.Visible = true
//Make checkbox with the name First
Word.ActiveDocument.FormFields.Add(Word.ActiveDocument.Range(0,0), Office.wdFieldFormCheckBox)
Word.ActiveDocument.FormFields(1).Name = “First”
Word.ActiveDocument.FormFields(“First”).CheckBox.Value = True
Word.Selection.Text = “My Name is First”
Word.Selection.MoveRight(Office.wdCharacter, 1)
Word.Selection.TypeParagraph
//Make checkbox with the name Second
Word.ActiveDocument.FormFields.Add(Word.ActiveDocument.Paragraphs(2).Range, Office.wdFieldFormCheckBox)
Word.ActiveDocument.FormFields(2).Name = “Second”
Word.ActiveDocument.FormFields(“Second”).CheckBox.Value = True
Word.Selection.MoveRight(Office.wdCharacter, 1)
Word.Selection.Text = “My Name is Second”[/code]
That’s what I meant… Well almost… If I populate an existing document (template.dotx), so I will not attribute the names using code.
I want to rename the checkbox controls manually in the template so I can select them in my code when I’ll be filling the report. What control and what property, in Word, I should use?
1- Check Box Form Field and the Field setting “Bookmark”
2- Content Control and “Title” or “Tag”
3- ActiveX Checkbox and “Name”
If I may ask you something else…
I’m struggling to add a worksheet after another in Excel. By default, it is added before the active worksheet. The “.add” parameter for after is the second (the before parameter being the first). If I write the code like this, I get a syntax error :
excel.Sheets.Add( ,excel.ActiveSheet)
If I replace the first parameter with Nil or False or “”, I get an OLEException.
The macro is like this :
Sheets.Add After:=Sheets(Sheets.Count)
What should I write before the comma to indicate that the first parameter is not used?
Thank you very much!
Roger