Automating Form Entry - Assistance from the masters, please.

[Waraq]

Hi All. I hope that you can help me. I am automating the entry of data to be popped into a template and I am using a combination of Field code, you know the stuff inside { } and VB. I am wondering a couple of things:
1) I am trying to do a calculation on the form. Basically if the numberUsers is a certain value, I need to calculate with different rates. Here is the rate table:
[ul][li]# Users monthly per user[/li]
[li]Up to 50 $150 n/a [/li]
[li]51-5,000 $2.50 [/li]
[li]5,001-10,000 $2.00 [/li]
[li]10,001+ $1.50 [/li][/ul]
The formula I am using is:

{IF {numberUsers}=< 50 150 &quot;{ IF { = AND ( { COMPARE { numberUsers } > 50 }, { COMPARE { numberUsers } <= 5000 } ) } = 1 numberUsers*2.50 &quot;{ IF { = AND ( { COMPARE { numberUsers } > 5000 }, { COMPARE { numberUsers } <= 10000 } ) } = 1 numberUsers*2 numberUsers*1.50}&quot;}&quot; \# &quot;$#,##0.00;($#,##0.00)}

2) I get number of users and other values by using the VB code below - I don't know if there is an easier way, I need user input for it:
Note: there are {REF bookmarks} throughout the form to capture the date entered.

Private Sub Document_New()
Dim bkm As Bookmark
Dim rngTemp As Range
Dim strTemp As String


Set rngTemp = ActiveDocument.Range(Start:=0, End:=0)
    
'Set Company Name
'MsgBox "begin Set Company Name"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="companyName", ValueText:=InputBox("Please enter the Company Name:", "Company Name")
Selection.Collapse Direction:=wdCollapseEnd

'Set Contact Name
'MsgBox "begin Set Contact Name"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="contactName", ValueText:=InputBox("Please enter the Contact Name:", "Contact Name")
Selection.Collapse Direction:=wdCollapseEnd

'Set Contact Title
'MsgBox "begin Set Contact Title"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="contactTitle", ValueText:=InputBox("Please enter the Contact's Title:", "Contact Title")
Selection.Collapse Direction:=wdCollapseEnd

'Set Number Of Users
'MsgBox "begin Set Num Users"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="numberUsers", ValueText:=InputBox("Please enter the Number of Users:", "# of Users")
Selection.Collapse Direction:=wdCollapseEnd

'Set Fixed Price
'MsgBox "begin Set Fixed Price"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="fixedPrice", ValueText:=InputBox("Please enter the fixed price of the deal:" _
    & Chr(13) & Chr(10) & "i.e. 15000", "Deal Price", "15000")
Selection.Collapse Direction:=wdCollapseEnd

'Set License Price
'MsgBox "begin Set License Price"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="licensePrice", ValueText:=InputBox("Please enter the price of each license:" _
    & Chr(13) & Chr(10) & "i.e. 60", "License Price", "60")
Selection.Collapse Direction:=wdCollapseEnd

'Set Support Price
'MsgBox "begin Set Support Price"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="supportPrice", ValueText:=InputBox("Please enter the price of support:" _
    & Chr(13) & Chr(10) & "i.e. 15", "License Price", "15")
Selection.Collapse Direction:=wdCollapseEnd

'Set Kickoff date
'MsgBox "begin Set KickOff Date"
ActiveDocument.MailMerge.Fields.AddSet Range:=rngTemp, _
    Name:="kickoffDate", ValueText:=InputBox("Please enter the estimated Kick-Off Date:" _
    & Chr(13) & Chr(10) & "i.e. January 1, 2004", "Kickoff Date", "January 1, 2004")
Selection.Collapse Direction:=wdCollapseEnd
    
ActiveDocument.Fields.Update
 
End Sub

3) The final piece is that I ask the user for a paragraph to personalize the document, didn't know how to do this in VB so I did it with a Form ASK statement

{ASK Notes “Please enter any notes pertaining to the customer:” \d "Mercury Technologies had the chance to speak with you about your infrastructure and we …"}

Thanks in advance for any help that you can lend.

 

[BrainB]

Might be able to help a bit. I think you can simplify your formula by using a lookup table and formula =VLOOKUP( ..., TRUE).

so the 2 column table would be something like :-
0 0
51 2.50
5,001 2.00
10,001 1.50
100,000 1.50

and the formula :-
=VLOOKUP(A8,$A$1:$B$5,2,TRUE)



Regards
BrianB
** Let us know if you get something that works !
================================

 

[Waraq]

Sorry I think that there was a misunderstanding...the rate table doesn't exist in the document; are you suggesting that I add it to it. Also, this is in Word.

Thanks.

 

[BrainB]

Ooops. Sorry - didn't read your post properly.

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[Waraq]

BrianB,

That ok, you seem to be the only one who does