Do not print command button

[ABabeNChrist]

I have a 2 page word document that uses bookmarks which are updated from a excel workbook and I would like to add a command button to the word doc that will not print, how would I do this
Here is the code I use

Code: Select all

    Dim xl As Object
    Dim wbk As Object
    Dim wsh As Object
    Set xl = GetObject(Class:="Excel.Application")
    Set wbk = xl.ActiveWorkbook
    Set wsh = wbk.Worksheets("Report Information Log")

    ActiveDocument.Bookmarks("Name").Range.Text = wsh.Range("B5").Value
    ActiveDocument.Bookmarks("Date").Range.Text = wsh.Range("E6").Value
    ActiveDocument.Bookmarks("Date2").Range.Text = wsh.Range("E6").Value
    ActiveDocument.Bookmarks("Address").Range.Text = wsh.Range("B30").Value
    ActiveDocument.Bookmarks("Fees").Range.Text = wsh.Range("E8").Value

    Set wsh = Nothing
    Set wbk = Nothing
    Set xl = Nothing

[HansV]

What do you want the command button to do?

In general, you need code to hide a command button just before printing, and to unhide it afterwards. See How to hide a “Print” commandbutton on a document form when a user clicks on it.

[ABabeNChrist]

I guess I would like the information updated first from excel workbook to the word doc then printed.
Should I run the code to update first then add the additional code as below

Code: Select all

    Dim xl As Object
    Dim wbk As Object
    Dim wsh As Object
    Set xl = GetObject(Class:="Excel.Application")
    Set wbk = xl.ActiveWorkbook
    Set wsh = wbk.Worksheets("Report Information Log")

    ActiveDocument.Bookmarks("Name").Range.Text = wsh.Range("B5").Value
    ActiveDocument.Bookmarks("Date").Range.Text = wsh.Range("E6").Value
    ActiveDocument.Bookmarks("Date2").Range.Text = wsh.Range("E6").Value
    ActiveDocument.Bookmarks("Address").Range.Text = wsh.Range("B30").Value
    ActiveDocument.Bookmarks("Fees").Range.Text = wsh.Range("E8").Value
    With ActiveDocument
        .Shapes(1).Visible = msoFalse
        .PrintOut Background:=False
        .Shapes(1).Visible = msoTrue
    End With

[HansV]

Sadly, I cannot get the code from the article I mentioned to work. I either get an error message or the button is printed with the document.

It might be better to use a custom Quick Access Toolbar button or ribbon button instead of a command button in the document.

[ABabeNChrist]

Thanks Hans, I went ahead an added a ribbon button. That seemed to be the best solution for my needs.

[macropod]

Do note there's a potential flaw in the way your code uses the bookmarks - if you output to them a second time, the new output will not replace what is already there; it will be prepended. It will also error-out if a bookmark gets deleted. If either of those is a possibility, try:

Code: Select all

Dim xl As Object
    Dim wbk As Object
    Dim wsh As Object
    Set xl = GetObject(Class:="Excel.Application")
    Set wbk = xl.ActiveWorkbook
    Set wsh = wbk.Worksheets("Report Information Log")
    wdDoc As Document
    Set wdDoc = ActiveDocument
    Call UpdateBookmark(wdDoc, "Name", wsh.Range("B5").Value)
    Call UpdateBookmark(wdDoc, "Date", wsh.Range("E6").Value)
    Call UpdateBookmark(wdDoc, "Date2", wsh.Range("E6").Value)
    Call UpdateBookmark(wdDoc, "Address", wsh.Range("B30").Value)
    Call UpdateBookmark(wdDoc, "Fees", wsh.Range("E8").Value)
    With wdDoc
        .Shapes(1).Visible = msoFalse
        .PrintOut Background:=False
        .Shapes(1).Visible = msoTrue
    End With
...
End Sub

Sub UpdateBookmark(Doc As Document, StrBkMk As String, StrTxt As String)
Dim BkMkRng As Range
With Doc
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
  End If
End With
Set BkMkRng = Nothing
End Sub

I'd also be inclined you use a cross-reference to your 'Date' bookmark instead of having the 'Date2' bookmark, since both are populated with the same date.

[ABabeNChrist]

Thanks Paul, worked great just had to add Dim before line

Code: Select all

wdDoc As Document