Microsoft Office Forums Word document with VBA and Form Fields reset when select File,Print

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2018, 05:33 PM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default 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 "}
Attached Files
File Type: docx sample fields.docx (23.9 KB, 1 views)

Last edited by fatal.lordes; 04-26-2018 at 06:05 PM. Reason: Added code.
Reply With Quote
  #2  
Old 04-26-2018, 09:18 PM
gmayor's Avatar
gmayor gmayor is offline Word document with VBA and Form Fields reset when select File,Print Windows 10 Word document with VBA and Form Fields reset when select File,Print Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,002
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
from the macro.
Attached Files
File Type: docm sample fields-1.docm (29.8 KB, 9 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 04-26-2018, 10:17 PM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-27-2018, 12:12 AM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default

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!
Reply With Quote
  #5  
Old 04-27-2018, 03:45 AM
gmayor's Avatar
gmayor gmayor is offline Word document with VBA and Form Fields reset when select File,Print Windows 10 Word document with VBA and Form Fields reset when select File,Print Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,002
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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.
Reply With Quote
  #6  
Old 04-27-2018, 02:15 PM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 04-27-2018, 02:40 PM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default

I have to say I have no idea how I got it to work - sheer luck - but suddenly it started working! LOL! Thanks Graham!
Reply With Quote
  #8  
Old 04-27-2018, 08:09 PM
gmayor's Avatar
gmayor gmayor is offline Word document with VBA and Form Fields reset when select File,Print Windows 10 Word document with VBA and Form Fields reset when select File,Print Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,002
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
Reply With Quote
  #9  
Old 04-27-2018, 08:16 PM
fatal.lordes fatal.lordes is offline Word document with VBA and Form Fields reset when select File,Print Windows 7 64bit Word document with VBA and Form Fields reset when select File,Print Office 2016
Novice
Word document with VBA and Form Fields reset when select File,Print
 
Join Date: Apr 2018
Posts: 6
fatal.lordes is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 04-27-2018, 11:30 PM
gmayor's Avatar
gmayor gmayor is offline Word document with VBA and Form Fields reset when select File,Print Windows 10 Word document with VBA and Form Fields reset when select File,Print Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,002
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Word document with VBA and Form Fields reset when select File,Print Recovering a word file (Select the encoding that makes your document readable) Canni Word 2 08-29-2012 02:46 PM
Word document with VBA and Form Fields reset when select File,Print Preventing Form Fields to Reset PosseJohn Word VBA 4 07-15-2011 09:44 PM
Word document with VBA and Form Fields reset when select File,Print Word Macro That Checks a Check Box Form Field When File Print is Executed DKerne Word VBA 4 06-09-2011 11:54 AM


All times are GMT -7. The time now is 11:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft