Need help converting a string to numeric...

[Michael 514]

Hi everyone!

Happy Holidays!

I hope someone can help me. It's really not a VBA thing, as the equation I will be using is simply right in the form itself.

Basically, in a protected for, I want to allow the user to choose 1 ? 10 from a drop down, and then in another cell, do a calculation with it.

Unfortunately, the choices in a dropdown are all text, so I can?t manipulate it.

For example, if the bookmark of the dropdown is Answer_A, I cannot exactly write in another field of my table the equation

= Answer_A * 100.

...because it things Answer_A is a string, and not a number.

In Excel, the command is simply =Value(Cell).

However, how do I do it in Word??

All this to say:

Basically, how do I convert the string in bookmarked Drop Down field, into a numeric bookmark that I can manipulate?


Your help is welcome!

Thanks!

Mike

[fumei]

Basically, in a protected for (sic), I want to allow the user to choose 1 ? 10 from a drop down, and then in another cell, do a calculation with it.

This is easy. Although I do not know what you mean by "another" cell. What is the first cell you are talking about...and, for that matter, what "another" cell are you talking about. A dropdown is not a cell - although it certainly could be IN one.


For example, if the bookmark of the dropdown is Answer_A, I cannot exactly write in another field of my table the equation
What do you mean by "bookmark of the dropdown"? Do you mean the name of the formfield? Formfields are in both the Formfields collection AND the Bookmarks collection. Why are you using the bookmark?

1. Look in Help. String and numbers (the various kinds) are data types. You want to convert data types...right? Try asking Help for that. It is in there.

2. You may need a little more help with getting the text value of the selected dropdown item. TEXT formfields can be accessed directing using the .Result property of the formfield. Dropdown formfield do not have a result. They have a .Value - but this is the value of the selected item in its index. To get the actual text of a selected item you need to use something like;
ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries _
.Item(ActiveDocument.FormFields("Dropdown1").DropDown.Value).Name
Be careful though, as this returns the actual text of the dropdown item. If you added any spaces to the item, this will be included.

[fumei]

Actually, could you describe your problem again? Please include your Word version.

The reason I ask is that I have absolutely no problem getting the selected item of a dropdown to multiply by 100 and the numeric result be placed into another cell. Granted, what is placed is the TEXT of the result.
Sub DotheMath()
With ActiveDocument
.Unprotect
.Tables(1).Cell(1, 2).Range.Text = _
.FormFields("Dropdown1").DropDown.ListEntries _
Item(.FormFields("Dropdown1").DropDown.Value).Name * 100
.Protect wdAllowOnlyFormFields, Password:=""
End With
End Sub
will take the selected item in a dropdown ( from 1 - 10), multiply it by 100, and put that value (as text) in cell(1,2) of Table(1).

What is your problem again?

[fumei]

Oh crap. I see. You want to do this without VBA. Hmmmm. I am not sure you can.

[Michael 514]

Hi there!

Okay, thank you both for the responses.... You are correct... my question was not 100% clear. And I am "hoping" that what I want to do can be done without VBA! :)

They say a picture is worth 100 words.... I hope this forum allows images...!

This is my form:

For the questions that are YES/NO, I need only put the following in the cells to the right of the drop down to make it work:
{IF{Answer_X="YES"{Answer_X_MAX}0}}

Lovely! If the user selects YES, the field to the right is populated with the 5 (or whatever the max value is). If the user selects NO, and it's a zero.

So, now you know what I am trying to achieve.
If the user selects "5" from that dropdown, then the number 5 appears.
If the user selects "6", then 6 appears.

In Excel, the equation would be =value(Answer_A). End of story.

Does such a thing exist in Word... Or do I have to crack open the VBA Editor?

I hope my image makes it clear!

I want to get from the Red to the Green!

Thanks!

http://pages.infinit.net/couple/pics/vba1.JPG

[fumei]

If the user selects YES, the field to the right is populated with the 5 (or whatever the max value is). If the user selects NO, and it's a zero.
The second sentence is wrong. Selecting No does NOT determine a result. The zero comes from the fact it is NOT Yes. If you had Yes, No, Maybe, Sometimes - No, Maybe, Sometimes would all give the same result...they are NOT Yes.

And this is your problem.

You could nest your IF statements. It would make a long series, but it could be done.

An alternative? Go to where you want the result. Insert a field using Ctrl-F9. You MUST insert the field that way. When you get the curly brackets, insert another field using Insert > Field. Using a Formula you can select the bookmark and multiply it.

Other than that, you are going to have to use VBA.

BTW: how are you updating the fields, if the document is protected?

[Michael 514]

Hey Gerry,

Thanks for writing back. Yes, you are correct. I just put in the YES/NO dropdown example to show you how I can 'convert' Yes/No's into point values.

I think that nesting TEN (LOL!) if statements is not the answer here ;)

If Answer_A="10",10,if Answer_B="9",9, etc.

If I am not mistaking, we are limited to 4 nested If statements.

And yes, your assumption is correct -- the field is prorected, and I put a little "ADD IT UP" button on top, which simply refers to the vba code to update fields three times.

Are you sure that I can have ten nested Ifs? If so, problem solved. Although it sure will be a little heavy.

By the way, what you said initially is interesting:

When you tried your VBA code, did it really put the number in the other cell as text?

Because that is exactly the problem.

When I try and do a "=sum(above)", no dice!

So looks like vba is the way to go, eh?

Thanks for your help!!

Mike

[fumei]

Yes, it works. Not only that, but if you have a =SUM(ABOVE) it will update without needing to remove protection.

ZIP File attached.

[Michael 514]

Gerry!
This is really cool!! Wow! Thanks!

Okay, now I would modify the code a bit to do something a tiny bit different.

Your code was this:

Sub DotheMath1()
With ActiveDocument
.Unprotect
.Tables(1).Cell(1, 2).Range.Text = _
.FormFields("Dropdown1").DropDown.ListEntries _
.Item(.FormFields("Dropdown1").DropDown.Value).Name * 100
.Protect wdAllowOnlyFormFields, Password:=""
End With
End Sub

Now, instead of storing the answer in a cell in the table, which is presumably what this line defines:

.Tables(1).Cell(2, 2).Range.Text = _

What would be the proper syntax to store your answer into a bookmark?

For example, let's say we want to take the value we calculated of Dropdown2 and store it into a bookmark entitled Q100.

That should solve my problem!

Thanks!!

[fumei]

First of all, again, is this a "normal" bookmark - that is, the bookmark was created using Insert > Bookmark; or is it a formfield (which is also listed as a bookmark.

If it is a formfield, then assuming you want to put the result into a text formfield:

ActiveDocument.FormFields("formfield_name").Result = _
ActiveDocument.FormFields("formfield_name").DropDown.ListEntries _
.Item(.FormFields("formfield_name").DropDown.Value).Name * 100

If it is a ordinary bookmark, then it is:
ActiveDocument.Bookmarks("bookmark_name").Range.Text = _
ActiveDocument.FormFields("formfield_name").DropDown.ListEntries _
.Item(.FormFields("formfield_name").DropDown.Value).Name * 100

Please note that if you are going to be re-using the bookmark range (that is, you require that the value may be repeatedly inserted), the above code will only work once.

If you need to be able to repeat, or change, it let me know. There is fairly simple code to do so.

[Michael 514]

Thanks again!
I didn't realize that there was a difference between a formfield and a bookmark.
All the other 100 bookmarks were created either by:

1 - selecting the field's answer and Insert, Bookmark. (ie for a calculated field)
or
2 - In the field settings box (as in a dropdown) selecting it from there.

The variable will remain static until the user hits the "Update Field" button, or if the user changes his mind, and changes the drop down.

I did notice one thing with your example... It always jumped back to the first selection on the dropdown (ie. 0). Is this normal, or can it be avoided?

Geez... I feel so bad taking up your time like this... I'd love to send you one of our forms for your analysis! I'll gladly throw a few bucks your way! You're offering tons of help to a total newbie!

Once we get it straightened out, we can paste the answer for all to see! Let me know! Thanks!