Form Field Formatting Challenge (%) (Word 97)

Topic

New Reply

Hey all, has anyone done this successfully?

Here is what I want to accomplish:

I have a protected form with form fields. I want to format it as %, but when I use the built in formatting, if I want to show 1% I need to type in .01. If I type in 1, the result is 100.00%, which is wrong.

It seems like every time I use % in the formatting, it automatically does the calculation. I’ve tried appending the % to the formatting as ‘%’ but it still calculates.

I’ve also tried writing code, but for some reason, even if I’m in the field, I get an error with the following code (on exit):
Selection.FormFields(1).Name because it doesn’t recognize the field I’m in as part of the selection????

I would much rather do it as formatting because I would like the negatives to show up as such, plus I don’t want a permanent % sign in the box because some of the fields are optional.

Any ideas?

Cecilia 🙂

Reply | Quote

Replies

Cecilia,

This sounds familiar – I think the same issue came up several months ago. The observation that came out of that was that it’s not really wrong, but is by design (i.e. “100.00%” is the percent representation of “1”).
The workaround (if I recall right) was to put the % symbol outside the formfield, and not use % in the formfield formatting.

You might try a search on this forum to turn up more details.

Gary

Reply | Quote

Yeah, I saw that answer, but unfortunately I can’t use it, as some ofthe fields are optional. I wish there were some way to access each individual field, but what I ended up doing was each time one of those fields was accessed, I ran code to change the answer to what I want. Unfortunately, since Selection wouldn’t work, I had to do it with ActiveDocument, so the whole process takes much longer than it should. Uggggh.

Reply | Quote

Cecilia,

I’d bet there is a way to make your code run faster – would you be able to post a sample? (and a sample of the form itself)

Because each form text field contains a bookmark, I’ve found it quite reliable to address/locate a text formfield by its bookmark name. You can also start by changing these bookmark names yourself (rather than using the Text1, Text2 etc. names that Word gives you), to make it easier to hook into them later via code.

Gary

Reply | Quote

I think this does what you’re referring to, but if you can find a way to make it better, I’d sure appreciate it!

Cecilia

Sub calcPercent()
Dim afield As FormField
Dim strValue As String
Dim strTemp As String
Dim blnNegative As Boolean
Dim rng As Range

blnNegative = False

ActiveDocument.Bookmarks("percents").Select
Set rng = Selection.Range
For Each afield In rng.FormFields
    If Left$(afield.Name, 3) = "per" Then
        strValue = afield.Result
        If strValue  "" Then
            If Right$(strValue, 1) = "%" Then
                strValue = Left$(strValue, Len(strValue) - 1)
            End If
            If Left$(strValue, 1) = "-" Then
                blnNegative = True
                strValue = Mid$(strValue, 2)
            End If
            If Left$(strValue, 1) = "(" Then
                blnNegative = True
                strValue = Mid$(strValue, 2, Len(strValue) - 1)
            End If
            If InStr(strValue, ".") = 0 Then
                strValue = strValue & ".00"
            Else
                strTemp = Mid$(strValue, InStr(strValue, ".") + 1)
                strValue = Left$(strValue, InStr(strValue, "."))
                If strValue = "." Then strValue = "0" & strValue
                If Len(strTemp) < 2 Then
                    strTemp = strTemp + "00"
                End If
                strTemp = Left$(strTemp, 2)
                strValue = strValue & strTemp
            End If
            If blnNegative = True Then
                strValue = "(" & strValue & ")"
            End If
            strValue = strValue & "%"
            afield.Result = strValue
        End If
    End If
Next afield

End Sub

Reply | Quote

Hi Cecila,

That’s pretty impressive code – too late on a Sunday night for me to parse that!

Have I got it right then, that you’ve got a bookmarked range of text, which range of text contains a number of formfields? (I was referring in my previous note to the formfield’s bookmarks; didn’t realize you had them all contained in an encompassing bookmark.)

Anyway, it might work just a little faster if you can replace:

ActiveDocument.Bookmarks(“percents”).Select
Set rng = Selection.Range

with:

Set rng = ActiveDocument.Bookmarks(“percents”).Range

Gary

Reply | Quote

Oooh! Working on Sunday????

Basically, I started with not wanting the page to jump. If I don’t have a bookmark for the area I want re-calculated, the whole document will run the macro (even if it’s just to find out that nothing should happen) and the whole thing will appear to jump. It still jumps with this solution, just not nearly as much as it did before.

The code just grew as I realized that every time you fill out a field, it becomes re-calcuable. I still have some more cases to take care of, but I’ve sent it out to be reviewed first, to make sure this is the approach we want. I’m sure they’ll provide me with a list of things I didn’t handle in the code 😉

Setting the range without selecting it sounds like a fine idea. That’ll probably also help with the jumping issues. Ahhh, it’s Monday and I still can’t wrap my mind around these things! 😉

Thanks for your help!

Cecilia

Reply | Quote

You’re welcome, and one other thing to note:

For preventing the screen from jumping around while a macro is running, don’t forget the old standby:

Application.ScreenUpdating = False

at the beginning of the procedure, and

Application.ScreenUpdating = True

at the end.

Gary

Reply | Quote