Drop down list to change a form field value?

[brainInjury]

Hi gang.
If you could indulge me for a moment I would greatly appreciate it.

I have a word (2000) document that has a drop down list and three form fields. I need to have the last form field down the page either add the two form fields above it, or be just a field that the user could enter a number manually. Depending on what is selected in the drop down list.

possible method: Out of four items in the drop down list, one would have the last form field as a "Type: Number" and the other three would have the last form field as a "Type: Expression" with the formula that would add the two form fields above it and display the results.

I would love to understand the code for this almost more than having it done for me; I've scoured the net for answer but with no luck. I don't think the drop down list items need to be assigned a variable as there are only four items. I was thinking of starting the code with a 'with' statement i.e.
Private Sub calcOn_Off()
With ActiveDocument.Bookmarks("dropdown1") '...etc.

but I'm not even sure if that's right. Anyway. any help would be appreciated.
Thanks,
Tony

[fumei]

It seems the dropdown list is not changing a formfield value, it is creating formfields. Is that correct? I am little confused, as you first state it HAS the three formfields, but then state it is to ADD formfields.

Please describe the process step-by-step.

I would love to understand the code for this almost more than having it done for meBravo! That is what I like to see.

Post details of what you want. Precisely. Exactly. In the smallest steps you can think of. I am sure you can get to what you want.

[brainInjury]

Hi Gerry,
Thanks for the uber-quick reply (I was trying to delete my other post when you responded) I'll do my best to explain my goal:

Firstly, what I meant by 'add' is actually the math equation - addidition, not adding another formfield. Now that i've confused you even more, let me backup.

the word document in question has:
a dropdown list with four possible choices.

three formfields, the last formfield has an equation that adds the numbers entered (by the user) in the first two formfields above it and displays the results.

everything works as it should with what I've created so far. however:

I would like to have the user enter his/her own total manually (in the last formfield) if a certain drop down list item is selected. In effect it would be the same as changing the last formfield from a "Type:equation" to a "Type:number" and back again depending on what was selected from the drop down list. This might not be the right approach to the problem. Perhaps I need to look at it from another angle; but it is generally what i'm aiming for.

I know from working in vba with excel that you can assign values to an outlook appointment i.e.

.Start = Date + 1 + TimeValue("19:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = "guitar lesson"
.Location = "The teachers house"

but i'm not sure if it's the same proceedure with a word formfield. i.e.
.type = number
.maxlength = 3
.defaultnum = "123"

so I'm a little confused in this respect; plus my fluency in vba is less than ideal.
Any suggestions would be great.
Tony

[lucas]

I don't understand why your not doing this in Excel.....

[brainInjury]

I know. This would be so easy in Excel. However, working in a hospital, there are specific protocols to follow. Besides that, I do think that Word does do a good job of making a form presentable to the end user where Excel is less about presentation (unless you're an accountant) but has more horsepower.

[lucas]

Word does do a good job of making a form presentable to the end user where Excel is less about presentation
Some of the most sophisticated forms are created in Excel....There is no limit (well almost) to what you can do to the appearance of an Excel document. Not to mention that your trying to do math....which is what Excel does. Word is a "Word Processor Program" Math does not come easily to Word.

If you have to present in Word.....do everything in Excel(easy)then put it in your Word doc as values...not formula's etc.

Accountants rarely use excel I have found....

just my 2cents.

[fumei]

I would like to help, but you did not describe it exactly. I will try again.

Describe it EXACTLY, and step-by-step. Let me see if I can demonstrate why it is not clear.
I would like to have the user enter his/her own total manually (in the last formfield) if a certain drop down list item is selected. In effect it would be the same as changing the last formfield from a "Type:equation" to a "Type:number" and back again depending on what was selected from the drop down list.

"Certain"....hmmm, yes, I know well what "certain" means.

Is the dropdown - which IS a formfield yes? - one of the "three formfields"? Or is it another one?

the word document in question has:
a dropdown list with four possible choices.

three formfields, the last formfield has an equation See. That sounds like it has FOUR formfields, the dropdown being the fourth.

You do not state what the dropdown items are, and what they are to do. You do not state what the "three" formfields are, although I can assume they are textbox formfields.

You do not state how you are actioning the "third" formfield, although I assume that it is done by an OnExit macro from the one before that.

But I don't KNOW.

There is no Type:equation. There is calculation.

everything works as it should with what I've created so far. however:That is nice, but you do not describe what and how you are making this work.

Again, all I can do is make assumptions. Formfield1 - user enters number, tabs to Formfield2, Formfield2 - users enters number, tabs....and the OnExit macro makes Formfield3 = Formfield1 + Formfield2.

Yes? No? How do I know if you do not state so?

If the user selects something different in the dropdown formfield are you going to lock them out of Formfield1, and Formfield2, and bring them directly to Formfield3 where they enter something?

I have no idea. It may be that you understand "exactly" and "precisely" differently.

Care to try again? Can you post a document?

[brainInjury]

wow. ok, I think my lack of clarity is a result of internalizing the process too much and not filtering the key elements up to a conscious level where I can translate what I'm thinking into a cogent explanation. I will try again (thanks for your patience!)

This Word document has these items:

-one drop down form field (located at the top of the Word document) with these categories in the drop down list:
Initial
Extension
Reduction
Termination

Further down the document...
- one text form field with the heading "Previously Approved Hours" set as 'Type: Number'

- one text form field with the heading "Change in Hours" set as 'Type: Number'

-one text form field with the heading "Total Approved Hours" set as 'Type: Calculation' the Expression for this form field adds the numbers entered in "Previously Approved Hours" and "Change in Hours" form fields and displays the results.

goal: if the user selects "Initial" from the drop down form field, that user can enter a number in the "Total Approved Hours" form field manually.

if the user selects any of the other three choices from the drop down form field that user will enter numbers into the "Previously Approved Hours" and "Change in Hours" form fields and have the "Total Approved Hours" form field calculate the sum of the two aforementioned form fields.

One of the issues with having a form field set at "Type: Calculation " is that you cannot alter it in any way. It will only display the results of the Expression which it is meant to calculate.

I think that covers it. I can't seem to attach the file I'm working on because it is larger than 19.5 Kb which is the max. allowed for this forum.

Lucas: I think the advantage Word has over Excel in terms of layout is the ability to Tab through the different form fields and allow the user to enter data quickly in specific places and in a certain order. Also (and it may seem trivial) but a Word document also "looks" like a sheet of paper in Print Layout Mode which seems more comfortable to users who are not used to Excel's white borderless sheets. Another advantage I find is when zooming in or out on a page, Word zooms from the middle of the page while Excel zooms from the top left corner - not as desireable in my case.

thanks for you interest.

[Dr@g0nfly]

Although it would seem that in this instance the questioner was seekign something a little different from the title of the thread, can anyone address the concept as it is titled? I AM looking to simply insert text into a form field value based on a drop down selection. (???) or perhaps I should start a new thread? Just didn't want to duplicate efforts. Thanks!

[Tommy]

I have the "Dropdown1" formfield call the SetForms sub on exit. Call CalFields on exit of "Text2", cause I couldn't get the "Text3" to recalculate and got tired of messing with it. :motz2: Thats why I prefer to just code it on a form and place it where I want it. :yes

Oh umm
one drop down form field - "Dropdown1"
"Previously Approved Hours" - "Text1"
"Change in Hours" - "Text2"
"Total Approved Hours" - "Text3"

All of the above items "Typed" and filled as specified. :thumb

Public Sub SetForms()
If ActiveDocument.FormFields("Dropdown1").Result = "Initial" Then
ActiveDocument.FormFields("Text1").Enabled = False
ActiveDocument.FormFields("Text2").Enabled = False
ActiveDocument.FormFields("Text1").Result = 0
ActiveDocument.FormFields("Text2").Result = 0
ActiveDocument.FormFields("Text3").Result = _
Val(InputBox("Please Enter the number of Approved Hours.", _
"Looking For Some Approval Here!"))
Else
ActiveDocument.FormFields("Text1").Enabled = True
ActiveDocument.FormFields("Text2").Enabled = True
ActiveDocument.FormFields("Text3").Result = 0
End If
End Sub
Public Sub CalFields()
ActiveDocument.FormFields("Text3").Result = _
Val(ActiveDocument.FormFields("Text1").Result) + _
Val(ActiveDocument.FormFields("Text2").Result)
End Sub

Edit: I reformatted so it doesn't get to close to 60 characters :)
Edit: per request I shoulda know I would miss one LOL

[fumei]

Can you edit it again? Use the underscore to put "Looking for some approval here!" on its own line. The code window stretches way over for me.

Thanks.

[fumei]

brain_Injury - how did you put in the Expression in textbox3?

Dragonfly - it is easy to do this. You have an OnExit macro for the dropdown fill in the text formfield(s). For example:

Sub ExitDropdown()
Dim oDocFF As Formfields

Set oDocFF = ActiveDocument.Formfields()
Select Case oDocFF("Dropdown1").Result
Case "yadda yadda"
oDocFF("Text1").Result = "yahoo...yadda yadda"
oDocFF("Text2").Result = ""
Case "Yogi Bear"
oDocFF("Text1").Result = ""
oDocFF("Text2").Result = "yabba dabba do"
' etc. etc. etc.
End Select
Set oDocFF = Nothing
End Sub
I do want to point out that unless you disable the text formfields, while you do fill them, the user will be able to change them.

Text formfields are generally for user input. If the requirement is to put text at a specific location, you may want to use bookmarks instead.

[Dr@g0nfly]

Thank you Fumei!

I have a drop down that is a word form field which is at present hiding/showing a bookmark value, narrowing the options in another drop down form field and if it's possible, will also be entering values in this language field.

So the scenario goes like this (code to follow):

User picks ddRegion
Based on value of ddRegion, {bkmk: VAT}*VAT Liability appears
Based on value of ddRegion, ddRgnRslt is determined
Based on value of ddRegion, txtLanguage is populated [this last function is where I need help]

Sub RegionExit()
Dim USD_Clearing(3) As String
Dim London(3) As String
Dim Frankfurt(3) As String
Dim i As Integer
Dim var

'your multiple lists here for price lists(dropdown #2)

USD_Clearing(0) = "SELECT"
USD_Clearing(1) = "Value 1"
USD_Clearing(2) = "Value 2"
USD_Clearing(3) = "Value 3"


London(0) = "SELECT"
London(1) = "Value 1"
London(2) = "Value 2"
London(3) = "Value 3"

Frankfurt(0) = "SELECT"
Frankfurt(1) = "Value 1"
Frankfurt(2) = "Value 2"
Frankfurt(3) = "Value 3"


ActiveDocument.Unprotect
Dim orange As Range
Dim Language As String
Set orange = ActiveDocument.Bookmarks("VAT").Range

Select Case ActiveDocument.FormFields("Region").DropDown.Value

Case 1
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Clear
With orange.Font
.Hidden = True
End With
Case 2
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Clear
For var = 1 To 10
ActiveDocument.FormField ("ddRgnRslt").DropDown.ListEntries.Add Name:=USD_Clearing(i)
i = i + 1
Next
ActiveDocument.FormFields("ddRgnRslt").DropDown.Value = 1
With orange.Font
.Hidden = True
End With

Case 3
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Clear
For var = 1 To 20
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Add Name:=London(i)
i = i + 1
Next
ActiveDocument.FormFields("ddRgnRslt").DropDown.Value = 1
With orange.Font
.Hidden = False
End With

Case 4
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Clear
For var = 1 To 20
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries.Add Name:=Frankfurt(i)
i = i + 1
Next
ActiveDocument.FormFields("ddRgnRslt").DropDown.Value = 1
With orange.Font
.Hidden = False
End With

End Select
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True,
End If
End Sub

[fumei]

I am not following.For var = 1 To 20
ActiveDocument.FormFields("ddRgnRslt").DropDown.ListEntries _
.Add Name:=London(i)
i = i + 1
Next
Please try and use the underscore character to shorten your code lines. Thanks.

OK. You have var = 1 To 20, so the code will run through 20 times. It clears the dropdown, and add 20 new entries, incrementing i each time.

Um... London() only has four items. So what are you doing here???

You mention problems with populating txtLanguage, but I see nothing regarding txtLanguage...so what is happening?

[Dr@g0nfly]

I shortened the code that is why there aren't 20 items. Also, those 20 items are confidential pricelist codes so I can't technically post them on the internet. I didn't follow through and change everything because that part of the code works fine.

What I was interested in was integrating some sort of code that would use the cases already set up (much like the show/hide) to populate a text field called txtLanguage. Based on the region (here country really), the language will be different.... thanks!

[fumei]

OK, I understand re: the 1 To 20. Fair enough.

If txtLanguage is a formfield, then:ActiveDocument.Formfields("txtLanguage").Result = string

[Dr@g0nfly]

Thanks Fumei!
I want to make sure I get this right, I should just insert that with string being in each case the value I want for the dropdown selection? Just making sure! I had some difficulty making this work, though now that I've left and come back to work, the syntax might have been slightly different and that makes all the difference! I'll give it go... :thumb

[mdmackillop]

I can't seem to attach the file I'm working on because it is larger than 19.5 Kb which is the max. allowed for this forum.
The limit is frustrating, however you can zip it and attach up to 244kb in size

[Dr@g0nfly]

Thank you thank you thank you!:cloud9:

[brainInjury]

Hi Guys,
Just an update and a big thank you to all! I did manage to take what learned here and apply it to my Word doc. Tommy, I took your code and removed the pop up box. when the user tabs out of the drop-down form field the maro runs and allows the user to either add the two form-fields down the page or disable the expression and manually enter the total amount. fantastic :thumb