Use Content Control Choices to Display Fees

[Tango63]

Greetings,

I'm new to VBA and trying to create a fillable membership application form in MS Word 2016 for a football club so that each applicant can complete and submit the form along with the correct membership fee.

There are 12 teams that can be selected from a Drop Down List CC - each team has its own membership fee. I want to display the fee elsewhere in the form's text in a separate field, based on which team is selected in the CC, so that the user knows how much to pay for the team selected.

It would be fine to hard code all 12 teams and their fees as they don't change very often.

Can anyone help?

Thanks very much.

T.

[gmaxey]

In the ThisDocument module of the VP Project there are built-in events. You could use the Document_ContentControlOnExit event like this:

Title you team dropdown list "Team Name" and title your plain text team fee "Team Fee" add this code.


Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
Select Case ContentControl.Title
Case "Team Name"
For lngIndex = 1 To ContentControl.DropdownListEntries.Count
If ContentControl.Range.Text = ContentControl.DropdownListEntries(lngIndex).Text Then
ActiveDocument.SelectContentControlsByTitle("Team Fee").Item(1).Range.Text = ContentControl.DropdownListEntries(lngIndex).Value
Exit For
End If
Next lngIndex
End Select
lbl_Exit:
Exit Sub
End Sub

[Tango63]

Hey Greg,

That's great! I now get the team selected in the dropdown list appearing in the fee text field.
But I'm not sure where to store the individual fees for each team. Where should they go?
I really appreciate your help.

Many thanks,

Tim.

[gmaxey]

You put the team name in the display as column of the dropdown and the fee in the value column.

[Tango63]

That's really helpful thanks; everything works how I wanted it to, so far.
Just one more thing, I want to copy the value displayed in the Team Fee field, subtract 15 from it (i.e. early payment discount) and paste the new figure elsewhere in the document into a simple text field.
Can this be done?

Many thanks again,

Tim.

[gmaxey]

Add another controls titled "Discount

ActiveDocument.SelectContentControlsByTitle("Discount").Item(1).Range.Text = ActiveDocument.SelectContentControlsByTitle("Team Fee").Item(1).Range.Text - 15

[Tango63]

You, sir, are a gent and a scholar!

Everything now working exactly as I wanted.

Thanks very much for your help - much appreciated!

20 bucks donated to your site - have a beer on me
:beerchug:

All the best...

T.

[gmaxey]

T.

Received and thank you.

I was intending to come back and update this thread this evening anyway. It worked but you just got lucky. The way dropdown DisplayName and Value work is that entries in each side of the list must be unique. So where:

TeamA 50
TeamB 60
TeamC 70

works ....

TeamA 50
TeamB 60
TeamC 50

won't.

To get around that limitation, create your list like this:

TeamA 1|50
TeamB 2|60
TeamC 3|50

and revise your code as follows:


Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
Dim arrParts() As String
Dim oCC As ContentControl
Select Case ContentControl.Title
Case "Team Name"
For lngIndex = 1 To ContentControl.DropdownListEntries.Count
If ContentControl.Range.Text = ContentControl.DropdownListEntries(lngIndex).Text Then
Set oCC = ActiveDocument.SelectContentControlsByTitle("Team Fee").Item(1)
arrParts = Split(ContentControl.DropdownListEntries(lngIndex).Value, "|")
oCC.Range.Text = arrParts(1)
ActiveDocument.SelectContentControlsByTitle("Discount").Item(1).Range.Text = oCC.Range.Text - 15
Exit For
End If
Next lngIndex
End Select
lbl_Exit:
Exit Sub
End Sub