#1
|
|||
|
|||
VBA to input / clear text from a Bookmark
Extreme Newbie. I have a word doc that is broken up into sections. The first page of each section has like fields eg "Site", "Client", "Project Manager", "Start Date" and so on. I have done some work with Excel and Access but am new to Word. I have created a user form that allows the user to enter the relevant data once, press a button and then auto populate the like fields on each section. The user is then able to tick a check box to select what section they want to print. The issue I have is once the user completes the fields and then presses the button to put the data into the relevant fields and the form prints.I am not able to clear the data in the doc for another entry to occur.
Any and all help would be greatly appreciated. Code:
Private Sub btnInputData_Click() Dim bmks As Bookmarks Dim bmRange As Range 'Pass the userform values to the document's bookmarks. Set bmks = ActiveDocument.Bookmarks Set bmRange = ActiveDocument.Bookmarks("bmSite").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite1").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite2").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite3").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite4").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite5").Range bmRange.Text = Me.txtSite.Value Set bmRange = ActiveDocument.Bookmarks("bmSite6").Range bmRange.Text = Me.txtSite.Value 'Set Client Set bmRange = ActiveDocument.Bookmarks("bmclient").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient1").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient2").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient3").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient4").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient5").Range bmRange.Text = Me.txtClient.Value Set bmRange = ActiveDocument.Bookmarks("bmclient6").Range bmRange.Text = Me.txtClient.Value 'Set Contact Number Set bmRange = ActiveDocument.Bookmarks("bmContactNo").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo1").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo2").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo3").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo4").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo5").Range bmRange.Text = Me.txtContactNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmContactNo6").Range bmRange.Text = Me.txtContactNumber.Value 'Set Project Manager Set bmRange = ActiveDocument.Bookmarks("bmProjectManager").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager1").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager2").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager3").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager4").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager5").Range bmRange.Text = Me.txtProjectManager.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectManager6").Range bmRange.Text = Me.txtProjectManager.Value 'Set Supervisor Set bmRange = ActiveDocument.Bookmarks("bmSupervisor").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor1").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor2").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor3").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor4").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor5").Range bmRange.Text = Me.txtSupervisor.Value Set bmRange = ActiveDocument.Bookmarks("bmSupervisor6").Range bmRange.Text = Me.txtSupervisor.Value 'Set ProjectNumber Set bmRange = ActiveDocument.Bookmarks("bmProjectNo").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo1").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo2").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo3").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo4").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo5").Range bmRange.Text = Me.txtProjectNumber.Value Set bmRange = ActiveDocument.Bookmarks("bmProjectNo6").Range bmRange.Text = Me.txtProjectNumber.Value 'Set Start Date and end Date Set bmRange = ActiveDocument.Bookmarks("bmStartDate").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate1").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate1").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate2").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate2").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate3").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate3").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate4").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate4").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate5").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate5").Range bmRange.Text = Me.DTPicker2.Value Set bmRange = ActiveDocument.Bookmarks("bmStartDate6").Range bmRange.Text = Me.DTPicker1.Value Set bmRange = ActiveDocument.Bookmarks("bmEndDate6").Range bmRange.Text = Me.DTPicker2.Value 'Print Sections If cbLocations.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,2-14", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbPitPipe.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,15-31", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbACM.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,32-62", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbHaul.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,63-73", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbDrill.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,74-89", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbAerial.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,90-100", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 ElseIf cbJointing.Value = "True" Then Dialogs(wdDialogFilePrint).Display Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:="1,101-113", PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 Else Exit Sub End If End Sub |
#2
|
||||
|
||||
A couple of things:
1. Instead of having a plethora of bookmarks for each replicated item, use just one and insert cross-references to it. 2. To correctly update the bookmarks use code like: Code:
Private Sub btnInputData_Click() Application.ScreenUpdating = False Dim wdDoc As Document Set wdDoc = ActiveDocument Call UpdateBookmark(wdDoc, "bmSite", Me.txtSite.Value) Call UpdateBookmark(wdDoc, "bmclient", Me.txtClient.Value) Call UpdateBookmark(wdDoc, "bmContactNo", Me.txtContactNumber.Value) Call UpdateBookmark(wdDoc, "bmProjectManager", Me.txtProjectManager.Value) Call UpdateBookmark(wdDoc, "bmSupervisor", Me.txtSupervisor.Value) Call UpdateBookmark(wdDoc, "bmProjectNo", Me.txtProjectNumber.Value) Call UpdateBookmark(wdDoc, "bmStartDate", Me.DTPicker1.Value) Call UpdateBookmark(wdDoc, "bmEndDate", Me.DTPicker2.Value) ' 'The rest of your code, from 'Print Sections' onwards, goes here ' wdDoc.Fields.Update Set wdDoc = Nothing Application.ScreenUpdating = True End Sub ' Sub UpdateBookmark(wdDoc As Document, BmkNm As String, NewTxt As String) Dim BmkRng As Range With wdDoc If .Bookmarks.Exists(BmkNm) Then Set BmkRng = .Bookmarks(BmkNm).Range BmkRng.Text = NewTxt .Bookmarks.Add BmkNm, BmkRng End If End With Set BmkRng = Nothing End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
And further, rather than all those lines of printing you could use anothwer subroutine:
Code:
Sub DoThePrinting(ThePages as String) Application.PrintOut FileName:="", Range:=wdPrintRangeOfPages, Item:= _ wdPrintDocumentWithMarkup, Copies:=1, Pages:=ThePages, PageType:= _ wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _ PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _ PrintZoomPaperHeight:=0 End Sub Code:
'Print Sections If cbLocations.Value = "True" Then Call DoThePrinting("1,2-14") ElseIf cbPitPipe.Value = "True" Then Call DoThePrinting("1,15-31") etc. etc. |
#4
|
|||
|
|||
Can anyone tell me why the code blocks are broken up like that. I posted them as one block.
|
#5
|
|||
|
|||
micko1, I hope you can see by using subroutines you get some advantages.
1. if you get the subroutine working propery ONCE, then it will wok for all instances; 2. having code chunked out (a subroutine that ONLY does the update of the bookmark) makes it easier to debug and test, as it has its tidy operations not mixed up with other processes; 3. it makes the code easier to write and easier to read |
#6
|
||||
|
||||
Quote:
[font][size][code]you code...[/size][font]....[/code] I have cleaned up the format tags. If you paste something, you may want to paste the text as "plain text". (Chrome has this option, not sure about other browsers). |
#7
|
|||
|
|||
Funny, I never put in any font changes. Must be a weird thing in IE.
|
#8
|
||||
|
||||
Ah, but did you copy/paste formatted content? I find copying/pasting from the vbe through IE works fine.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Thanks
I would like to thank all of you that have replied to this. Your examples work great and are exactly what I was looking for. I did not know about referencing but do now.
Thanks again for your assistance, very much appreciated. Mick |
#10
|
|||
|
|||
Paul, nope. Straightforward Crtl-C and Ctrl-V right from the VBE.
|
#11
|
||||
|
||||
It appears that it will paste the format info. If your browser doesn't have a "paste as plain text" option, you may need to copy to a pure text editor first. I used to copy/paste that way until Chrome added the option, I use it a lot.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Replacing text with user input.?.?.? | brad1977 | Word | 3 | 11-20-2012 10:20 AM |
Open header to insert text into bookmark | Amapola188 | Word VBA | 3 | 07-12-2012 05:16 PM |
Repeating Bookmark Text | BECKTHOMO | Word | 1 | 03-27-2012 08:34 PM |
Word 2003 - IncludeText Does Not Include Bookmark Text if in a Form Text Control | skarden | Word | 1 | 12-12-2011 10:39 PM |
delete all bookmark text | hklein | Word VBA | 4 | 08-10-2011 04:33 AM |