Field Types in Forms?

[Angyl]

I tried searching for the answer to this in VB help and didn't find it, and I
also tried searching on this board.

I've seen enough examples of using a macro to insert a regular form field:

Type:=wdFieldFormTextInput
.Name = "text2"
.Enabled = True

What I need to know is what the NAME is to insert other types of Form fields
like calculation fields, specifically. What goes after

Type:=??????

And then where would you put the calculation you want the field to have?

 

[Greg Maxey]

Angyl,

This is a bit tricky. The constant you need is wdCalculationText but
you can't apply it directly. You have to add the field and then edit
the type:

Sub ScratchMacro()
Dim myFormField As FormField
Set myFormField = ActiveDocument.FormFields.Add(Range:=Selection.Range,
_
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText,
Default:="=Text1 + Text2", _
Format:="0.00", Enabled:=False

End Sub

 

[Angyl]

Man...Greg...You like...

*sob*

SO RULE!!

I'm gettin' choked up, here...

 

[Greg Maxey]

Judging from your other posts, if you want the negative number
formatted with red font and positive green, you will need to use a a
regular field vice formfield. You can set up the format switch as
follows:

Sub ScratchMacro2()
Dim myField As Field
Dim oRng As Range
Set myField = ActiveDocument.Fields.Add(Range:=Selection.Range, _
Type:=wdFieldEmpty, _
Text:="=Text1 + Text2 \# ""#.00;(#.00);0""", _
PreserveFormatting:=False)

Set oRng = myField.Code
With oRng.Find
.Text = "#.00"
.Replacement.Font.Color = wdColorGreen
.Execute Replace:=wdReplaceOne
oRng.Collapse
.Text = "(#.00)"
.Replacement.Font.Color = wdColorRed
.Execute Replace:=wdReplaceOne
End With
End Sub

 

[Greg Maxey]

Maybe I was a little quick to complain that you didn't acknowledge
replies the other day. I am now truly humbled by your outpouring of
praise. Control yourself ;-)

 

[Angyl]

Greg...

Superman...

Last question for this project, (I think).

I'm gonna throw a wrench in the works that you just gave me (which works
perfectly, by the way).

I need to adjust the code you gave me to dynamically change WHAT text fields
are in the calculation.

See, I have the users enter the Admin Fee in say Field1, Field2, Field3, and
Field4

The first calculator in the table is automatically set to calculate from
Field1.
The added field by the code you gave me, needs to, the second time, get its
data from Field2
And if another row is added, Field3 and then Field4 and so on and so forth.

On second thought, I may be going about this the whole wrong way:

I know how to create a user form thanks to you.
I know how to create a user form that does math based on user input, thanks
to school this past semester.

What I don't know how to do is have the userform CREATE a text box in the
document...and input the sum of the calculation in there.

That would work for this project, too.

 

[Greg Maxey]

Angyl,

Here is one way, assuming that your variable fields are in column 1 and
2 of a table and the calculation field is in the same table and row.
You could use cell references instead of the field bookmark names:

Sub ScratchMacro()
Dim myFormField As FormField
Dim i As Long
Dim pFrmStr As String
i = Selection.Cells(1).RowIndex
pFrmStr = "=A" & i & "+ B" & i
Set myFormField = ActiveDocument.FormFields.Add(Range:=Selection.Range,
_
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText,
Default:=pFrmStr, _
Format:="0.00", Enabled:=False
End Sub

 

[Angyl]

Boy do I feel watched over.

But no, this isn't about the red/green switch. (That was yesterday's
project which is now complete, and the Bosses are smiling, thanks largely to
you). The new Project needs... Well let me see how you'd do this:

There's a form with a table in it, single row, four columns. The fourth
column must contain a calcuation using secret information that should not
print on the form. Before I came along, this was done on a physical
calculator and the result simply inputted in the field.

If it were that simple, I could do that myself using hidden text and a
calcuation field.

Problem is, you START with a single row, but say the client you're working
on has 2 calcuations needed...

or three....

or four...

up to SEVEN.

So I need to be able to ask the user "Do you want to add a new code?" and if
they say yes, create the new row. (I've got this part down). The problem is
that now the calculations will change for that new row (same basic
inputs...different numbers).

I can think of about four different ways, with my limited knowledge of
Word/Forms/Macros/VB programming to do this, but the actual code is what is
tripping me up.

The easiest way, I think, would be to work in a User form that asks for the
basic numbers, does the calculations, PUTS the result in the Word document,
and asks the user if they want to do it again. If yes...insert new row in
document...clear data in userform...and then on submit, put the result in the
new fields in the table in the document and ask the user again...

Like I said, I've created user forms that do math when a button is pressed.
And I've created user forms that ask questions and put text in PRE-EXISTING
fields in a document.

What I do not know how to do is get a user form to actually create a field
in a table and put something in it.

 

[Angyl]

I am learning... I just piddled around with VB a bit and figured this much
out:

..Tables(1).Rows.Add

Which works. Now all I need is a way to CREATE a field in that table in a
specific location and give it a specific name and I'll be done.

 

[Greg Maxey]

Ok so you have a 4x1 table and you have a userform (UF) with a command
button to add a new row. In column 4 of that new row you want a
calculation field with variables set to cells 1, 2 and 3 of that same
row.

In the form use something like the following to command a new row:

Private Sub CommandButton1_Click()
Me.Tag = "Add Row"
Me.Hide
End Sub


In the project use something like the following to show the form and
add the row/field:

Sub CallUF()
Dim myFrm As UF
Set myFrm = New UF
myFrm.Show
If myFrm.Tag = "Add Row" Then
AddRow
End If
Unload myFrm
Set myFrm = Nothing
End Sub
Sub AddRow()
Dim oDoc As Document
Dim myRng As Word.Range
Dim myFormField As FormField
Dim i As Long
Dim pFormulaStr As String
Set oDoc = ActiveDocument
oDoc.Tables(1).Rows.Add
i = oDoc.Tables(1).Rows.Count '(+/- whatever to return the actual new
row number)
pFormulaStr = "=A" & i & " + B" & i & " + C" & i
Set myRng = ActiveDocument.Tables(1).Cell(i, 4).Range
myRng.Collapse wdCollapseStart
Set myFormField = ActiveDocument.FormFields.Add(Range:=myRng, _
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText, _
Default:=pFormulaStr, _
Format:="0.00", Enabled:=False
End Sub

No promises that this method is actually proper or robust. Doing
things with userform is not a strong suit. Not that I have a strong
suit.

 

[Angyl]

Your suit's a heckuva lot stronger than mine, pal. All that code you wrote
blows me away just when I was starting to think I understood a little bit...

Thank you, very much, I will play with it in a little bit when the boss gets
off my case for everything else I've been neglecting while streamlining HIS
business with these new forms...

People (like you) can get paid SOOOO much for knowing all this stuff. What
do I get?

Exploited!

LOL Thanks again, Greg. I'll let you know how it turns out.

 

[Greg Maxey]

LOL, with the exception of a few benevolent souls that have made small
contributions via my website, I am on the same ship as you ... the USS
EXPLOITED ;-)

I didn't actually know how to do this when you asked, I was just
determined to figure it out. BTW, you can replace the lingering
AcitveDocument throughout the code with oDoc. I did some cut and paste
from the earlier postings and forgot to abbreviate.

Good luck.

 

[Angyl]

Exploited???

Did you go to school to learn all this or are you eeking it out an
understood piece of code at a time like me?

I'm having some trouble with the code you gave me and it's not complicated.
I got the part where I added the little code to a command button (which seems
to do nothing, but I guess it's setting up the next part)

But where do I put the rest of that code? You said:
"In the project use something like the following to show the form and
add the row/field:"

Does that mean it's a macro I should be adding to a button actually in the
document or something? (I haven't tried that yet). I'm just confused as to
exactly where that larger piece of code goes.

Thanks. I feel better being on the U.S.S. exploited knowing I have such
company...

Then again, it tells me I've got a long way to go before I can get off!

 

[Greg Maxey]

Angyl,

No school and I have never cracked the cover of a VBA textbook. What little
I know I have picked up by reading and responding to newsgroup questions
like yours and asking lots of questions of this same newsgroups when I got
stumped.

The command button code is simply setting a value for the userform .Tag
member and then hidding the form from view. It is the procedure (I guess
you would call it a macro) "addrow" that is called in the Sub CallUF()
procedure that actually adds the field.

Open a new blank document and then press ALT+F11

This opens the VB editor. If not showing, show the Project Explorer window
by pressing CTRL+r

In the directory of the Project Explorer you should see Project (Document1)

Click on it and then press Insert>Module

Modules
Modules 1 will be added to the directory tree.

Double click on Module 1

In the big code window on the right type - Option Explicit

Click Tools>Options>Editor and check everything in that dialog.

Now paste the macro text I sent you:
Sub CallUF()
Dim myFrm As UF
Set myFrm = New UF
myFrm.Show
If myFrm.Tag = "Add Row" Then
AddRow
End If
Unload myFrm
Set myFrm = Nothing
End Sub


See the section "Technical details" at
http://gregmaxey.mvps.org/Custom_MsgBox.htm for a bit of discussion how the
userform is declared, initialized, shown, hidden, unload and finally killed.

You can see from the code above, when the user presses the command button in
the Userform then the .Tag value is going to be read in the code and call
the macro AddRow which is follows:

To see all of this happen, just step through the code using the F8 key.



Sub AddRow()
Dim oDoc As Document
Dim myRng As Word.Range
Dim myFormField As FormField
Dim i As Long
Dim pFormulaStr As String
Set oDoc = ActiveDocument
oDoc.Tables(1).Rows.Add
i = oDoc.Tables(1).Rows.Count '(+/- whatever to return the actual new
row number)
pFormulaStr = "=A" & i & " + B" & i & " + C" & i
Set myRng = oDoc.Tables(1).Cell(i, 4).Range
myRng.Collapse wdCollapseStart
Set myFormField = oDoc.FormFields.Add(Range:=myRng, _
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText, _
Default:=pFormulaStr, _
Format:="0.00", Enabled:=False
End Sub





Sub CallUF()
Dim myFrm As UF
Set myFrm = New UF
myFrm.Show
If myFrm.Tag = "Add Row" Then
AddRow
End If
Unload myFrm
Set myFrm = Nothing
End Sub
Sub AddRow()
Dim oDoc As Document
Dim myRng As Word.Range
Dim myFormField As FormField
Dim i As Long
Dim pFormulaStr As String
Set oDoc = ActiveDocument
oDoc.Tables(1).Rows.Add
i = oDoc.Tables(1).Rows.Count '(+/- whatever to return the actual new
row number)
pFormulaStr = "=A" & i & " + B" & i & " + C" & i
Set myRng = ActiveDocument.Tables(1).Cell(i, 4).Range
myRng.Collapse wdCollapseStart
Set myFormField = ActiveDocument.FormFields.Add(Range:=myRng, _
Type:=wdFieldFormTextInput)
myFormField.TextInput.EditType Type:=wdCalculationText, _
Default:=pFormulaStr, _
Format:="0.00", Enabled:=False
End Sub

 

[Angyl]

Greg,

Been to your website. Well done, first of all. And secondly let me extend
my thanks to your wife for her service and sacrifice for our country. Yes, I
know you're the one in the Navy, but as a husband of a former Naval officer
myself, I know some of what SHE goes through. But many thanks to you as
well for your service. All the time my wife spent in the navy all I wanted
was for her to get me on a submarine...

so much power... so beautiful...*sniff*

But it never happened.

As to the subject at hand, all I can say is my brain is fried. I'm
impressed as all hell that you know as much as you do after four years of
swimming through these forums, but then again, you're a Naval officer... I
know you're damn good.

I got your code workin', barely before I passed out. My boss and I are much
obliged.

:

 

[Greg Maxey]

Angyl,

Once again your approbation overwhelms. I will pass on your compliments to
my wife and my hat is off to you as well as a former Navy spouse. I really
did learn the little I know about VBA by nosing around in these news groups
and through the patients of fellows like Steve Hudson, Jay Freedman,
Jonathan West, Doug Robbins, Dave Lett, Peter Hewitt, Jean-Guy Marcill,
Helmut Weber, Tony Jolans; the ladies like Cindy Meister, and the it
Jezebel. All have taught me something and most have taught it to me more
than once. The real thanks and approbation goes to them all.

Yes a submarine is an awesome thing. Justice to them has never been done by
Hollywood. My days of service on submarines are over and I will unlikely
ever ride or board one again, but ah the memories.

Good luck with your picky boss and projects.