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

Open in new window

GrahamSkan

Wherever possible in Word (Excel too), use a range object or property instead of the Selection.

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.

Sub InsertTextInBookmark(bmk As Word.Bookmark, strText As String)
    Dim rng As Word.Range
    Dim strBookmarkName As String
    
    strBookmarkName = bmk.Name 'capture name
    Set rng = bmk.Range
    rng.Text = strText
    're-add bookmark in case it was overwritten and
    'to ensure that it contains the text
    rng.Document.Bookmarks.Add strBookmarkName, rng
End Sub

Open in new window

You can use the same technique to paste into a bookmark:

Sub PasteIntoBookmark(doc As Word.Document, strBookmarkname As String)
    Dim rng As Word.Range
    
    Set rng = doc.Bookmarks(strBookmarkname).Range
    rng.Paste
    doc.Bookmarks.Add strBookmarkname, rng
End Sub

Open in new window

Setting bookmarked text to Hidden is simply:

doc.Bookmarks("bmk1").Range.Font.Hidden = True

Open in new window

GrahamSkan

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

Open in new window

EricdYoung

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

 Doc.Bookmarks("one").Range.Font.Hidden = Not Sheet1.CheckBox1.Value

Open in new window

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

Open in new window

ASKER CERTIFIED SOLUTION

EricdYoung

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EricdYoung

ASKER

not all code is within excel but it works