MS Office Forum / Word / Programming / May 2006
Field Types in Forms?
|
|
Thread rating:  |
Angyl - 10 May 2006 15:51 GMT 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 - 10 May 2006 17:18 GMT 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 - 10 May 2006 17:52 GMT Man...Greg...You like...
*sob*
SO RULE!!
I'm gettin' choked up, here...
> Angyl, > [quoted text clipped - 12 lines] > > End Sub Greg Maxey - 10 May 2006 18:08 GMT 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 - 10 May 2006 18:12 GMT 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.
> Angyl, > [quoted text clipped - 12 lines] > > End Sub Greg Maxey - 10 May 2006 18:42 GMT 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
Greg Maxey - 10 May 2006 18:01 GMT 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
Angyl - 10 May 2006 18:51 GMT 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.
> 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 [quoted text clipped - 20 lines] > End With > End Sub Angyl - 10 May 2006 19:00 GMT 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.
> 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 [quoted text clipped - 20 lines] > End With > End Sub Greg Maxey - 10 May 2006 19:53 GMT 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 - 10 May 2006 20:33 GMT 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.
> 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 [quoted text clipped - 44 lines] > things with userform is not a strong suit. Not that I have a strong > suit. Greg Maxey - 10 May 2006 20:50 GMT 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 - 10 May 2006 22:10 GMT 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!
> 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 [quoted text clipped - 6 lines] > > Good luck. Greg Maxey - 10 May 2006 22:33 GMT 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
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> Exploited??? > [quoted text clipped - 30 lines] >> >> Good luck. Angyl - 10 May 2006 23:51 GMT 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 - 11 May 2006 00:26 GMT 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.
 Signature Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word.
> Greg, > [quoted text clipped - 19 lines] > > "Greg Maxey" wrote:
|
|
|