"spell dollars" function for WORD

[merlizbet]

Hey folks,

I'm creating a simple WORD fill-in form for a co-worker to print checks. To help avoid errors, I was going to attempt to add a custom WORD function that would translate the number-formatted dollar amount into the long-hand dollar amount. I've found and am using a function written by someone else to do this trick in Access. I'd call myself a "Big F/little t", Functional/technical person and I'm generally relatively successful doing stuff with VBA in Access; but I really have no idea how to (or if I can) use this same (or modified) function in WORD. I copy/pasted the Access VBA into a WORD VBA module, but a very quick Google search hasn't even told me how I could call it. Can anyone give me pointers on how to go about translating Access VBA into WORD VBA (if that's possible) and then calling it. That's probably too broad of a question, but I needed to start somewhere.

Thanks for you input!

 

[SkipVought]

hi,

"I've found and am using a function written by someone else to do this trick in Access."

Please post your VBA code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TonyJollans]

If you are using 'simple' VBA then you should be able to cut and paste it into Word. If you are using Access facilities they will have to be rewritten.

As for running code in Word, it rather depends when, or in response to what, you want it to run. Are you entering your amount into a text box or a content control, or just on the document surface? Would it suffice, say, to run once on a button click, or document save or close, to identify and process all dollar amounts?


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[merlizbet]

Code is here...

https://www.microsoftaccessexpert.com/Microsoft-Access-Code-Number-to-Words.aspx

 

[SkipVought]

As Tony stated, just supply your number in the function and the text should return.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[merlizbet]

I'm using "legacy" fill-in fields -- because I know how to make them work. I started out with "content controls" (I guess they're called), but I couldn't get the bookmark bit to dynamically repeat the field. The user will type things like check date, check amount, and payee into the Remittance section and then that data can just repeat in the Check section. I guess at some point I'll try to figure out how to do it with the new "content controls", but this was such a simple little thing I went for the easy/known method.

 

[merlizbet]

How do I call the function from a WORD fill-in field?

 

[TonyJollans]

You can put Exit macros on legacy Form Fields - they're not perfect and don't respond to mouse clicks but work well if you're tabbing through the Form.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

You want the field for numeric dollars and cents to supply the function argument and fill a Lable field with the text from the function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

How do I call the function from a WORD fill-in field? "

If you have amount in a field "Amt_Field" and you want to show it spelled out in a field called "Spelled_Field"

With this function:

Public Function fSpellNumber(ByVal MyNumber)
...

you do:

Spelled_Field = fSpellNumber(Amt_Field)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[merlizbet]

I copy/pasted the function into a new WORD VBA module. I renamed the function fSpellNumber.

In the Remittance area I have a field for:
Check Date: { FORMTEXT }
for which I've created a Bookmark named Date.
And in the Check area I have:
{ REF Date }
which dynamically displays the date from the Check Date field that was typed into the Remittance area. (Note: It doesn't display the Bookmark data until I click out to the print preview, so maybe it works on Exit, I'm not sure. But that's OK.)

Also in the Remittance area I have a field for:
Amount: { FORMTEXT }
for which I've created a Bookmark named Amount
I currently have a fill-in field for written-dollars in the Check area so the user can just fully type out the dollar amount. As some tests, to call the function, I tried swapping out this fill-in field for:
{ fSpellNumber(REF Amount) } and also
{ REF fSpellNumber(Amount) }
But neither of those do anything.

Thoughts/comments?

 

[SkipVought]

You can't assign the function directly in the fields!

Look at Andy's post @ 17:25

So in your module you have the fSpellNumber function and a macro...

Sub StickAmount()
   Spelled_Field = fSpellNumber(Amt_Field) 
End Sub

...that you call On Exit from the Amount Field.
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TonyJollans]

Follow what others have said to invoke the macro but you will still need to pull some more bits together ...


There are all sorts of options but, when the document is protected for forms some of them won't work. I would use a "Document Variable".

In the Check area put { DOCVARIABLE "DollarAmount" } (using Ctrl+F9 to put in the braces)

Now create a VBA routine ..

[blue]Sub ExitSub
    ActiveDocument.Variables("DollarAmount") = 
        fSpellNumber(ActiveDocument.FormFields("Amount").Range.Text)
End Sub[/blue]

Back in the document, double click on the Amount {FORMTEXT} field and click on the dropdown labeled "Exit" and select "ExitSub" (or whatever you've called it).


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[merlizbet]

OK, Tony, I *think* I was up with y'all on everything (including your last post to add another procedure, which I named TranlateAmount), but then when I tried to lock the form, that's not an option. I ran into this same problem yesterday when I was attempting to build this using "content controls". The locking problem disappeared yesterday when I switched over to using the legacy fill-in fields.

Thoughts?

 

[SkipVought]

Do the coding et al before locking the form

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[merlizbet]

Found it (via more Google searching)! It was in Design Mode and "Start Enforcement" is grayed out if it's in Design Mode.

 

[merlizbet]

Holy crap! It's working!

I must have missed one of you saying I also needed to check the field property box "Calculate on Exit". Via a breakpoint, I could see the code was working to calc the written dollar amount, but it wouldn't display on the form even when I went to print preview. Took another look at the field Properties. Saw that "Calc on Exit" checkbox and thought I give that a try. Not only did that fix the code-calc-display, but when I checked that box for the other fields, their values immediately displayed in the Bookmark REF fields, too. So *everything* works better now!

Thanks to all for your help!!

 

[Andrzejek]

I am expecting one of those checks to come my way.
Don't bother to fill in the amount, I'll do it myself...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Ha! Andy has one of those macros!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[merlizbet]

How 'bout if I attach the template here. Then you can fill in the payor, too. :-D