RE: Saving changes made in a form by gordon(dot)bentleymix(at)gmail(dot)com>
gordon(dot)bentleymix(at)gmail(dot)com>
Mon Oct 15 18:59:03 PDT 2007
Leu,
The basic process is as follows:
Create an AutoNew macro that runs when you â??launchâ?? the template to create a
new document from it. This macro sets up some variables to store the values
that go into your userform and one additional variable to store the â??statusâ??
of the document; i.e. is it a new document or one thatâ??s been created
previously and youâ??re now opening for editing?
Then in the code that actually creates the document (by clicking OK in the
userform or whatever other method you use) store the values from the userform
in the variables you set up when you first launched the template.
Then create a â??rerunâ?? macro that just loads and redisplays the original
userform. The initialisation code in the userform checks the â??statusâ??
variable and if itâ??s a document thatâ??s been created previously, loads the
variable information stored in the document into the appropriate controls on
the userform.
In the example below I have a userform called â??frmOtherDetailsâ?? that has one
textbox control on it called â??txtDocTitleâ?? and a command button called
â??btnOKâ?? (as well as a â??Cancelâ?? button). In addition to the userform I have an
AutoNew module and a module called â??RerunOtherâ??. And in the body of the
template itself, I have a bookmark called â??txtDocTitleâ?? for inserting the
value from the textbox into the document. You could use a formfield or some
other method, but if you use a bookmark, itâ??s important that you understand
how this works.
This should be enough for you to figure out what Iâ??m doing.
NOTE: A good portion of the code in the "real" template has been omitted
from the following for clarity. The code thatâ??s reproduced here just shows
how to save the values from the userform and reload the values back into the
userform when you rerun the template.
CODE IN THE â??AutoNewâ?? MODULE
Sub AutoNew()
â??*** Create the necessary variables and then load and show the userform ***
CreateDocVars
Load frmOtherDetails
frmOtherDetails.Show
End Sub
Sub CreateDocVars()
â??*** Create two variables: one for storing the â??statusâ?? of the document and
one for the value that will be entered in the UserForm; you can create as
many as you need for storing the values from your userform. ***
With ActiveDocument.Variables
.Add ("DocNew")
.Add ("DocTitle")
End With
End Sub
CODE BEHIND THE â??frmOtherDetailsâ?? USERFORM
Private Sub UserForm_Initialize() â?? Initialise the userform
â??*** If the â??DocNewâ?? variable has a value of â??Falseâ?? (i.e. this is an
existing document thatâ??s being rerun, not a new document just being created)
then load the variables. ***
If ActiveDocument.Variables("DocNew").Value = "False" Then LoadDocVars
End Sub
Private Sub LoadDocVars()
â??*** Load the value from the â??DocTitleâ?? variable into the â??txtDocTitleâ??
textbox on the userform; this only happens if itâ??s not a new document. ***
txtDocTitle.Value = ActiveDocument.Variables("DocTitle").Value
â??*** Also because â??txtDocTitleâ?? is a required field (in my template) I donâ??t
have to check to make sure thereâ??s a value in the variable. However, if it
was an optional field Iâ??d probably modify this a bit like this: ***
â?? If ActiveDocument.Variables("DocTitle").Value <> â?? â?? Then
txtDocTitle.Value = ActiveDocument.Variables("DocTitle").Value Else Then
txtDocTitle.Value = â??â??
â??*** Otherwise you get a single space loaded into the textbox, which is the
value that gets saved in the variable if thereâ??s no value in the textbox
originally. ***
End Sub
Private Sub btnOK_Click()
â??*** When you click the â??OKâ?? button, hide the userform, save the values from
the userform into the variables, insert the values from the userform into the
document and then unload the userform. ***
frmOtherDetails.Hide
SaveDocVars
InsertValues
Unload frmOtherDetails
End Sub
Private Sub SaveDocVars()
â??*** Save the values from the userform into the variables: ***
With ActiveDocument
â??*** the value from the â??txtDocTitleâ?? textbox into the â??DocTitleâ?? variable;
***
.Variables("DocTitle").Value = txtDocTitle.Value
â??*** and a value of â??Falseâ?? into the â??DocNewâ?? variable to indicate that itâ??s
not a new document anymore. ***
.Variables("DocNew").Value = "False"
End With
End Sub
Private Sub InsertValues()
â??*** Insert the value from the â??txtDocTitleâ?? textbox into the document by
first selecting the bookmark (not pretty, I know, but necessary â?? Iâ??ll
explain why)... ***
ActiveDocument.Bookmarks("DocTitle").Select
With Selection
â??*** then set the text of the selection to the value from the textbox ***
.Text = txtDocTitle.Value
â??*** then reinsert the â??DocTitleâ?? bookmark around the selection (this is the
reason you have to select the bookmark to begin with). ***
.Bookmarks.Add "DocTitle", .Range
End With
End Sub
CODE IN THE â??RerunOtherâ?? MODULE
Public Sub RerunOther()
â??*** Just load and show the userform; the code in the userform
initialisation subroutine does the heavy lifting. ***
Load frmOtherDetails
frmOtherDetails.Show
End Sub
Conclusion
This is a _really_ simple example. Some of the others templates I use are
much more complicated. For example, I build lists of values for the attendees
of a meeting and need to save these into variables so the document can be
rerun. In this case, I create the variables dynamically when I create the
document. I also delete them and recreate them dynamically when I rerun the
document. And the code for inserting the values into the document is also
more complex; however, itâ??s still the same concept: select the requisite
bookmark, insert the text into it and reapply the bookmark.
Hope this answers your question!
--
Cheers!
The Kiwi Koder
"LEU" wrote:
> It is the first one. I creadted a form in VBA which is called up from a
> toolbar button. The user updates then information and then saves the changes
> back to the document. Thank you for your help.
>
> LEU
>
>
>
> "NZ VBA Developer" wrote:
>
> > Leu,
> >
> > Before I go to a lot of work posting a reply that might not be on point, I'd
> > like to ask for a bit more information.
> >
> > When you say "a user opens a form to edit a document", are you talking about
> > a userform that displayed through some VBA process like an AutoNew macro? Or
> > do you mean a form with form fields? If it's the first situation then I've
> > got an answer, but if it's the second... well, I'm sure I could work it out
> > but I don't have anything directly at hand that will help.
> >
> > --
> > Cheers!
> > The Kiwi Koder
> >
> >
> > "LEU" wrote:
> >
> > > If a user opens a form to edit a document, is there a way to save the
> > > original information of the changes made in the document as Variables? If it
> > > can be done is there a way to call it back up later for review?
> > >
> > > LEU