EricdYoung
asked on
Hide text in word document from excel checkbox
Thank you in advance and I'm completely burnt out on looking for this answer. I have an excel file used for data entry. I use the below code in excel to merge data into a word documents using bookmarks. This word document is a report that has every possible paragraph needed. These paragraphs are bookmarked differently then the below code and, of these, I only need certain bookmarks to show. The word document opens when I click "Ok" on the text message once the merging is complete. My question is I want to have check boxes in Excel that will hide, or tell word to hide, the associated bookmarked text in the now open word document? Also, is it possible to check the checkboxes in excel before hitting the merging marco?
Sub AutoReport()
'The purpose of this program is to remove a lot of tedious work around writing and if necessary updating
'reports. The program operates on the following principle:
'It will read all bookmarks from the word document specified and match them with named ranges in Excel with
'the same name. In the case of charts it will use the chart names.These bookmarks and named ranges require
'the following naming convention:
'For text items: "ARTextXXX"
'For tables: "ARTableYYY"
'For charts: "ARChartZZZ"
'What the program does is load all the bookmarks into a list (also known as an array).
'Each bookmark will be assigned a number 'within the reange 1 to x (x is specified below).
'These bookarks are then filtered into three different 'categories using the above naming convention.
'This is necessary as a different operation has to be carried 'out for text, tables and charts.
'The program then works with the word document and jumps to the bookmarks location and deletes the current
'content. It then deletes the bookmark manually (as Word has an issue with inserting items into bookmarks
'and often deletes them itself). The program inserts the item and then re-instates the bookmark and moves
'on to the next item.
'To enable the same item to be copied multiple times from excel the bookmarks in Word can use the
'following naming convention:
'ARTextChartorTable_01
'ARTextChartorTable_02
'etc.
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim FilePath As String
Dim FileName As String
Dim n As Long
Dim p As Long
Dim pcount As Long
Dim x As Long
Dim Prompt As String
Dim Title As String
Dim StrDoc As String
Dim BkmNo As Long
Dim temp As Variant
Dim MyRange As Word.Range
Dim BkmTxtSplit As Variant
Dim BkmChartSplit As Variant
Dim BkmTableSplit As Variant
Dim wdRng As Object
'Things to make sure that the application runs properly and speeds things up.
With ThisWorkbook
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo ErrorHandler
'Assign the Word file path and name to variables. This program looks for the file name in the named
'range "AR_ReportName".
Set wdApp = Nothing
FilePath = ThisWorkbook.Path
FileName = Range("AR_ReportName")
StrDoc = FilePath & "\" & FileName
'Opens the word document.
Set wdDoc = wdApp.Documents.Open(StrDoc)
'Populates array with bookmark names.
With wdDoc
ReDim BkmNames(1 To wdDoc.Bookmarks.Count)
For BkmNo = 1 To wdDoc.Bookmarks.Count
BkmNames(BkmNo) = wdDoc.Bookmarks(BkmNo).Name
Next
End With
'If you want to see what Word is doing remove the comment charachter from the line below.
'wdApp.Visible = True
'Define x and pcount. X is the maximum number of bookmarks in the word document. pcount needs to be
' 3 times higher as it is used as a status indicator and there are three different operations being
'carried out cycling through all bookmarks each time.
x = 100
pcount = 300
'creates an array with all the Bookmarks that are text based
n = 0
p = 1
ReDim BkmTxt(1 To x)
For Each temp In BkmNames
n = n + 1
If Left(BkmNames(n), 5) = "ARTex" Then
BkmTxt(n) = BkmNames(n)
End If
Next
'creates an array with all the Bookmarks that are table based
n = 0
ReDim BkmTable(1 To x)
For Each temp In BkmNames
n = n + 1
If Left(BkmNames(n), 5) = "ARTab" Then
BkmTable(n) = BkmNames(n)
End If
Next
'creates an array with all the Bookmarks that are table based
n = 0
ReDim BkmChart(1 To x)
For Each temp In BkmNames
n = n + 1
If Left(BkmNames(n), 5) = "ARCha" Then
BkmChart(n) = BkmNames(n)
End If
Next
'PASTE TEXT STRINGS LOOP
n = 1
For Each temp In BkmTxt
p = p + 1
Prompt = "Please wait. Copying text. Carrying out operation " & p & " of " & pcount & "."
Application.StatusBar = Prompt
'If The Bkmtxt(n) is empty then go to the next one, once that has been found do next operation.
If BkmTxt(n) = Empty Then
n = n + 1
'should find match and work
Else
'You might want to use multiple copies of the same text string.
'In this case you need to call the bookmark as follows: "ARTextWhatever_01"
'You can use as many bookmarks as you want.
BkmTxtSplit = Split(BkmTxt(n), "_")
vValue = Range(BkmTxtSplit(0)).Text
Set wdRng = wdApp.ActiveDocument.Bookmarks(BkmTxt(n)).Range
If Len(sFormat) = 0 Then
'replace the bookmark text
wdRng.Text = vValue
Else
'replace the bookmark text with formatted text
wdRng.Text = Format(vValue, sFormat)
End If
'Re-add the Bookmark
wdRng.Bookmarks.Add BkmTxt(n), wdRng
n = n + 1
End If
Next
'PASTE TABLES LOOP
'This part of the program copies all the tables from the Excel sheets to the word document. It does so
'by matching the bookmarks with the named ranges. There is also a rather complicated operation with two
'temporary bookmarks. These need to be used, as Word deletes bookmarks when pasting into them. The way
'it is done is as follows.
'This program tells Word to go to the bookmark, delete the contents of the bookmark (cleaner that way)
'and then deletes the bookmark iteself. It then inserts the first temporary bookmark. Then it pastes
'the table into the document. It also centres the table and also removes double line spacing.
'After that it adds a space " " and then inserts the second temporary bookmark and another space.
'It then selects the area between the two bookmarks and adds the permanent bookmark again with the
'same name. It then deletes the two temporary bookmarks so that they can be reused for the next loop.
n = 1
For Each temp In BkmTable
p = p + 1
Prompt = "Please wait. Copying tables. Carrying out operation " & p & " of " & pcount & "."
Application.StatusBar = Prompt
If BkmTable(n) = Empty Then
n = n + 1
Else
'This removes the everything after the "_" in the bookmark so that the bookmark and the named
'range can be matched up.
BkmTableSplit = Split(BkmTable(n), "_")
'Copy the required table
Range(BkmTableSplit(0)).Copy
'Go to the where the table should be pasted
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=BkmTable(n)
'Delete the current bookmark and the current selection so that everything is nice and clean.
wdApp.ActiveDocument.Bookmarks(BkmTable(n)).Delete
wdApp.Selection.Delete
'Add temporary bookmark one in front of the table
wdApp.Selection.Move Unit:=wdCharacter, Count:=0
wdApp.ActiveDocument.Bookmarks.Add "Bkmtmp1"
'Paste the table
wdApp.Selection.PasteAndFormat (wdPasteDefault)
'Add temporary bookmark 2 and include a space (char32) - this is a bodge fix
wdApp.Selection.TypeText Text:=Chr(32)
'Dead Code wdApp.Selection.Move Unit:=wdCharacter, Count:=1
wdApp.ActiveDocument.Bookmarks.Add "Bkmtmp2"
'Add another space for good luck
wdApp.Selection.TypeText Text:=Chr(32)
'Select the area encapsulated by the two temorary bookmarks
wdApp.ActiveDocument.Range( _
wdApp.ActiveDocument.Bookmarks("BkmTmp1").Range.Start, _
wdApp.ActiveDocument.Bookmarks("BkmTmp2").Range.Start) _
.Select
'Centre the table
wdApp.Selection.Tables(1).Rows.Alignment = wdAlignRowCenter
'Make sure that the line spacing is not double (it normally is)
wdApp.WordBasic.CloseParaBelow
'Add the original bookmark again
wdApp.ActiveDocument.Bookmarks.Add BkmTable(n)
'Delete the temporary bookmarks so that they can be re-used
wdApp.ActiveDocument.Bookmarks("Bkmtmp1").Delete
wdApp.ActiveDocument.Bookmarks("Bkmtmp2").Delete
'Add 1 to n so that the program goes to the next value.
n = n + 1
'Empty the clipboard
Application.CutCopyMode = False
End If
Next
'PASTE CHART LOOP
'This part of the application copies all the charts in the Excel document and pastes them into the
'word document. It uses the same methodology for keeping the bookmarks as the tables part of the program.
'Please ensure that you define what sheet you are copying your tables from (see below).
'Reset n to 1
n = 1
For Each temp In BkmChart
'Status indicator
p = p + 1
Prompt = "Please wait. Copying charts. Carrying out operation " & p & " of " & pcount & "."
Application.StatusBar = Prompt
'If BkmChart is empty see if the next one isn't
If BkmChart(n) = Empty Then
n = n + 1
'If there is something in BkmChart carry out the following operation
Else
BkmChartSplit = Split(BkmChart(n), "_")
'Copy the required table. IMPORTANT: You have to define what sheet your chart is to be
'copied from. All charts must be in the same sheet.
ThisWorkbook.Sheets("Charts").ChartObjects(BkmChartSplit(0)).Copy
'Go to the where the table should be pasted
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=BkmChart(n)
'Delete the current bookmark and the current selection so that everything is nice and clean.
wdApp.ActiveDocument.Bookmarks(BkmChart(n)).Delete
wdApp.Selection.Delete
'Add temporary bookmark one in front of the table
wdApp.Selection.Move Unit:=wdCharacter, Count:=0
wdApp.ActiveDocument.Bookmarks.Add "Bkmtmp1"
'Paste the table
wdApp.Selection.PasteAndFormat (wdPasteDefault)
'Add temporary bookmark 2 and include a space (char32) - this is a bodge fix
wdApp.Selection.TypeText Text:=Chr(32)
'Dead Code wdApp.Selection.Move Unit:=wdCharacter, Count:=1
wdApp.ActiveDocument.Bookmarks.Add "Bkmtmp2"
'Add another space for good luck
wdApp.Selection.TypeText Text:=Chr(32)
'Select the area encapsulated by the two temorary bookmarks
wdApp.ActiveDocument.Range( _
wdApp.ActiveDocument.Bookmarks("BkmTmp1").Range.Start, _
wdApp.ActiveDocument.Bookmarks("BkmTmp2").Range.Start) _
.Select
'Add the original bookmark again
wdApp.ActiveDocument.Bookmarks.Add BkmChart(n)
'Delete the temporary bookmarks so that they can be re-used
wdApp.ActiveDocument.Bookmarks("Bkmtmp1").Delete
wdApp.ActiveDocument.Bookmarks("Bkmtmp2").Delete
'Add 1 to n so that the program goes to the next value.
n = n + 1
'Empty the clipboard
Application.CutCopyMode = False
End If
Next
'Turn everything back on
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.StatusBar = False
'Let the user know the procedure is now complete
Prompt = "The procedure is now completed."
Title = "Procedure Completion"
MsgBox Prompt, vbOKOnly + vbInformation, Title
'Make our Word session visible
wdApp.Visible = True
'Clean up
Set wdApp = Nothing
Set wdDoc = Nothing
End With
ErrorExit:
Set wdApp = Nothing
Exit Sub
'Error Handling routine
ErrorHandler:
If Err.Number = 5174 Then
MsgBox "Please check the file name you specified is correct."
Resume ErrorExit
Else
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not wdApp Is Nothing Then
wdApp.Quit False
End If
Resume ErrorExit
End If
'Last edited by ZappAstrim; August 25th, 2011 at 21:41.
End Sub
This shows how to use each type of check box if on a Worksheet:
Dim Doc As Word.Document
'Checked box means show text. Unchecked for hide text
'Form checkbox
Select Case Sheet1.Shapes("Check Box 1").ControlFormat.Value
Case xlOn
Doc.Bookmarks("one").Range.Font.Hidden = False
Case xlOff
Doc.Bookmarks("one").Range.Font.Hidden = True
End Select
'ActiveX checkbox
Doc.Bookmarks("one").Range.Font.Hidden = Not Sheet1.CheckBox1.Value
ASKER
Wow, Graham you lost me on the first response lol. I'm still a novice in vba, so I'm sorry. I've tried using the codes provided and i'm having no success. The Range Object code just seem to open up the new macro window and the font hidden code only works if I put the check box in the word document.
Is there a way to modify
I can use the below code in a checkbox on the word document which it works great. I just want to have the check box in excel.
Is there a way to modify
Doc.Bookmarks("one").Range.Font.Hidden = Not Sheet1.CheckBox1.Value
to hide the bookmark labeled "one" in the word document?I can use the below code in a checkbox on the word document which it works great. I just want to have the check box in excel.
.Bookmarks("one").Range.Font.Hidden = CheckBox1.Value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
not all code is within excel but it works
To put text into a bookmark, save the range into a Range object. Set the bookmark's text to the new text, and re-add the bookmark. You can use this Sub if you need to do it several times.
Open in new window
You can use the same technique to paste into a bookmark:Open in new window
Setting bookmarked text to Hidden is simply:Open in new window