#1
|
|||
|
|||
Word document with VBA and Form Fields reset when select File,Print
Hi All
New to VBA for Word as well as forms for Word. Have created something that works great where a date is requested on file load, which then calculates dates on a form. Problem is, when we select File, Print, the dates all revert to original inputted date. Sample of prompt to get date, place got date and one calculation from got date included below (I got this last one off the internet and it calculates the prior fortnight) is attached. Couldn't work out how to copy the code and paste in here so attachment was only option (sorry). As said, everything works fine until I select File, Print. Even tried turning off "update fields when print" but made no difference. Hoping someone can shed some light on the subject. I found a macro that lets me copy out the field data. So below is the prompt to get the date to calculate from, then display the date entered, and then one of the calculations (basically each is the same except the second row changes to 12, to 11, to 10, etc, so a fortnight is done): { SET FNDATE { FILLIN "What is the Fortnight Ending (in DD/MM/YYYY format)" \@ "dd/MM/yyyy" } } { FNDATE } {QUOTE {SET Delay -13} {SET a{=INT((14-{FNDATE \@ M})/12)}} {SET b{={FNDATE \@ yyyy}+4800-a}} {SET c{={FNDATE \@ M}+12*a-3}} {SET d{FNDATE \@ d}} {SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}} {SET e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}} {SET mm{=i+3-12*INT(i/10)}} {SET yy{=100*e+g-4800+INT(i/10)}} "{dd}-{mm}-{yy}" \@ " dd/MM/yyyy "} Last edited by fatal.lordes; 04-26-2018 at 06:05 PM. Reason: Added code. |
#2
|
||||
|
||||
There are no macros in your document. What you posted was a field calculation.
I never did like FillIn fields. The attached version uses a content control date picker to get the end date (which presumably will always be a Friday) and then populates the table based on that selection, using a macro. If you want the end date to be any day of the week, remove the line Code:
EndDate = EndDate - Weekday(EndDate) + 6
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thanks for that. I didn't include the macro as all it does is say update the fields on load of file. I did it that way as wasn't sure how to do it all via macro.
|
#4
|
|||
|
|||
That looks fantastic, Graham. Word macro is a bit of an enigma to me so not exactly sure on how it works, but it works and it can print, too! Will try an nut it out tomorrow as my job tomorrow will be to integrate that into the current form, removing my stuff that works until it comes to printing. Thank you, thank you, thank you!
|
#5
|
||||
|
||||
The code is simple enough - annotated below:
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Dim oTable As Table Dim EndDate As Date Dim oCell As Range Dim i As Long, j As Long If ContentControl.Tag = "Date" Then 'Process only the date content control If ContentControl.ShowingPlaceholderText = False Then 'Only process if it is not showing the placeholder text 'Get the end date from the content control EndDate = CDate(ContentControl.Range.Text) 'If it is not a Friday, get the next Friday EndDate = EndDate - Weekday(EndDate) + 6 'Define which table to process (here table 2) Set oTable = ThisDocument.Tables(2) 'Process the cells in the first row With oTable.Rows(1) 'From cell 4 to cell 17 For i = 4 To 17 'adjust the count to allow for the unwanted cells j = 13 - i + 4 'Set a range to each cell in turn Set oCell = .Cells(i).Range 'omit the end of cell character from the range oCell.End = oCell.End - 1 'write the date in the cell oCell.Text = Format(DateAdd("d", -j, EndDate), "dd/mm/yyyy") Next i End With 'Process the cells in the third row With oTable.Rows(3) For i = 4 To 17 'eveything as row 1 j = 13 - i + 4 Set oCell = .Cells(i).Range oCell.End = oCell.End - 1 'except the value is the first three letters of the day of the week oCell.Text = Format(DateAdd("d", -j, EndDate), "ddd") Next i End With End If End If End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com Last edited by gmayor; 04-27-2018 at 11:28 PM. |
#6
|
|||
|
|||
Thanks Graham. I'm trying to work out how you've linked the fortnight ending date to the table dates as when I copy and pasted into my form, the link seems to have been lost. Will keep trying to see how it links.
|
#7
|
|||
|
|||
I have to say I have no idea how I got it to work - sheer luck - but suddenly it started working! LOL! Thanks Graham!
|
#8
|
||||
|
||||
The code uses the content control 'Date' to establish the end date.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#9
|
|||
|
|||
Yep, figured that bit out but it was the linking of that to the cells in the table which auto update that I'm not 100% sure about. Originally the link kept breaking because nothing would happen, then suddenly the link worked and I'm not really sure what I did. All good though, I'll work it out. Really appreciate your sharing your knowledge with me.
|
#10
|
||||
|
||||
The code is annotated to demonstrate how it works. The code runs on exit from the content control, so you have to click outside it, having selected a date. If there is no date or you don't click outside the control nothing happens.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't reset form fields (Word 2013) and conditional formatting | rogelinepaula | Word VBA | 24 | 01-07-2016 03:54 PM |
Extract form fields to Word Document | RonNCmale | Word VBA | 22 | 01-11-2014 05:06 AM |
Recovering a word file (Select the encoding that makes your document readable) | Canni | Word | 2 | 08-29-2012 02:46 PM |
Preventing Form Fields to Reset | PosseJohn | Word VBA | 4 | 07-15-2011 09:44 PM |
Word Macro That Checks a Check Box Form Field When File Print is Executed | DKerne | Word VBA | 4 | 06-09-2011 11:54 AM |