cell formatting

[Darryl]

Greetings,
I have a table in my word document. One of the cells is numeric in format.
I have set the number format
to 0.00%. The problem is when I enter 8.7 it converts it to 870%. I want
it to be 8.7%.

How do I do this ? Do I have to create a macro that mathematically converts
it ?

thanks,
-Darryl

 

[Jean-Guy Marcil]

Darryl was telling us:
Darryl nous racontait que :

Greetings,
I have a table in my word document. One of the cells is numeric in
format. I have set the number format
to 0.00%. The problem is when I enter 8.7 it converts it to 870%.
I want it to be 8.7%.

How do I do this ? Do I have to create a macro that mathematically
converts it ?

Enter
.087
instead.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Darryl]

Yes, of course I already knew that.

Rather, I want to enter 8.7 and have

the cell show 8.7%.

how do I accomplish that ?

 

[Suzanne S. Barnhill]

In addition to adding a number format, you'll have to do some math to divide
by 100.

 

[Darryl]

OK then.
How can I create a macro to divide the cell contents (that I am exiting) by
100 ?

thanks,
Darryl

 

[Suzanne S. Barnhill]

No macro needed. The fact that you have already applied a number format
suggests that you're using a field. If it's an = field or (as sounds more
likely) a calculated form field, all you have to do is add /100 to the
calculation.

 

[Darryl]

It's not calculated. The user types in the value. The formatting was to
get it a percentage.
So, the user types in 8.7 and the formatting changes it to 8.7%.

 

[Anne Troy]

Hi, Darryl. The whole confusion about percentage formatting is that 8.7 is
not 8.7%. 8.7 percent formatted as a decimal is really 0.087, so that needs
to be entered, or you need to divide it by 100 so it works the way you want
it to. This also confuses Excel users.
*******************
~Anne Troy

www.OfficeArticles.com

 

[Darryl]

Thanks Anne.

I understand that 8.7 is not 8.7%. But as the users say, I need it this
way.
I don't know how to make it such that when the user leaves the field in the
table, that the resulting value (870%) is divided by 100 so it shows 8.7%
(the goal).

If someone could explain how I can do the division I would be greatly
appreciative.

-Darryl.

 

[Anne Troy]

Sorry, Darryl. Hope I didn't sound condescending--didn't mean to.
The easiest way to accomplish this would be to put the % outside the form
field and just format the cell as 0.00. Otherwise, methinks you'll need a
macro.
*******************
~Anne Troy

www.OfficeArticles.com

 

[Suzanne S. Barnhill]

Okay, I see what's happening, and yes, I suppose it will require a macro
unless there's some way to perform calculations on data a user enters.
Unfortunately, I have little experience with forms and no expertise with
macros, so someone else will have to help.

 

[Jean-Guy Marcil]

Darryl was telling us:
Darryl nous racontait que :

Thanks Anne.

I understand that 8.7 is not 8.7%. But as the users say, I need it
this way.
I don't know how to make it such that when the user leaves the field
in the table, that the resulting value (870%) is divided by 100 so it
shows 8.7% (the goal).

Here is a little macro that does that:

'_______________________________________
Sub Divide100()

Dim myValue As Single

With ActiveDocument.Bookmarks("Value1").Range
myValue = CSng(Left(.Text, Len(.Text) - 1))
myValue = myValue / 10000
.FormFields(1).Result = CStr(myValue)

End With

End Sub

'_______________________________________

Note that I have to divide by 10 000 because when I do the calculation, the
number entered by the user a=has already been multiplied by a 100 because of
the number format...

The formfield name is Value1.
Either change the name in the macro, or change it in the form itself.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Darryl]

Well,
I changed Value1 to my field name.
I have the field set as number, format 0.00%, macro on exit is
divide100.

When I run it, I get a type mismatch error. Looking at the macro,
I'm not sure why ? Form field is type number. Is the macro assuming
the field is text ?

 

[Darryl]

Well, maybe isn't not a type mis-match. When I try to enter data in the
field and then leave it,
nothing happens (ie, 1.2 gets set to 120%). When I debug the macro it gives
an error saying that
the macro tries to access a protected area of the document. If I unprotect
the document, (ie, not a form), then nothing happens (as described above).

 

[Jean-Guy Marcil]

Darryl was telling us:
Darryl nous racontait que :

Well,
I changed Value1 to my field name.
I have the field set as number, format 0.00%, macro on exit is
divide100.

When I run it, I get a type mismatch error. Looking at the macro,
I'm not sure why ? Form field is type number. Is the macro assuming
the field is text ?

I created a protected form with a text field set to number with the 0.00%
format, then I protected the form. My macro works. I just checked again, and
it worked. So we are doing things differently.
Describe how you created the field (and gave it a name) where the user
enters the value and the document itself (how is it protected, what does it
contain).

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jean-Guy Marcil]

Darryl was telling us:
Darryl nous racontait que :

Well, maybe isn't not a type mis-match. When I try to enter data in
the field and then leave it,
nothing happens (ie, 1.2 gets set to 120%). When I debug the macro
it gives an error saying that
the macro tries to access a protected area of the document. If I

OK, what Word Version?

unprotect the document, (ie, not a form), then nothing happens (as
described above).

That is normal because OnExit macros do not work when the document is not
protected for forms.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Darryl]

I can send you the exact document as I might not answer properly. I am
using Word 2000
BTW, the document is a simple, one page form.

 

[Darryl]

I created a brand new document with a table (inserted from the
forms toolbar) that had three columns. first, I put the insertion point
into column 1 row 1. Then selected text form field from the forms toolbar.
I then right clicked, chose properties and set the a field type to number.
Text format to 0.00%. Selected divide100 as the exit macro. Under the
field settings group box I set the bookmark to value1. Clicked OK.
Clicked form protect button on forms toolbar.

Then to test, put insertion point in column1 row 1 and typed 1.23 then
pressed the tab key. Immediately the type mis-match error 13 pops up.
press debug on the error dialog box and the debugger says the offending is:
myValue = CSng(Left(.Text, Len(.Text) - 1))

what am I doing wrong ?

thanks,
Darryl

 

[Doug Robbins]

Leave the formfield set as a text formfield and run a macro on exit from the
formfield that contains the following code:

With ActiveDocument.FormFields("Text1")
.result = Format(.result / 1000, "0.00%")
End With

replace "Text1" with the bookmark name assigned to the formfield.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jean-Guy Marcil]

Darryl was telling us:
Darryl nous racontait que :

I can send you the exact document as I might not answer properly. I
am using Word 2000

Ha! Word 2000! I had done all my tests in Word 2003. Protected documents
behave differently in almost all versions of Word...

Try this instead:

'_______________________________________
Sub Divide100()

Dim myValue As Single

ActiveDocument.Unprotect

With ActiveDocument.FormFields("Value1")
myValue = CSng(Left(.Result, Len(.Result) - 1))
myValue = myValue / 10000
.Result = CStr(myValue)

End With

ActiveDocument.Protect wdAllowOnlyFormFields, True

End Sub
'_______________________________________

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org