Multiple entries in dropdown lists

paul_pearson · #1 04-02-2013, 06:49 AM

Hi

Can i create a Template with dropdown list of 35 companies to choose from using the following examples to show what i require:

If i select Roundhouse Nurseries or Smithswholesales from the list it also includes Phone Number,Fax Number and Email address for each company....can i have about 35 companies in the dropdown list to choose from....i will only select 1 company at a time as this will be for a invoice (will create a template)....i want it to show the email address but not have the blue underline

Roundhouse Nurseries
Phone: 08 1111 1111
Fax: 08 2222 2222
Email: sales@roundhousenurseries.com.au

Smiths Wholesales
Phone: 08 3333 3333
Fax: 08 4444 4444
Email: sales@smithswholesales.com.au

Thanks

Paul
Excel 2010

macropod · #2 04-02-2013, 08:16 PM

You could add the entries to a dropdown content control. As the dropdown only supports a single line per entry, the other data could be output to a plain text or rich text content control via an on_exit macro (i.e. you must exit the dropdown content control after updating it for the conditional output to appear). See attached demo. If you examine the dropdown, you'll see the Client names appear. And, if you examine the dropdown's properties, you'll see that for each client name, the 'Value' field contains the dependent data, with the different output lines separated by | characters, as in:
Phone: 08 1111 1111|Fax: 08 2222 2222|Email: sales@roundhousenurseries.com.au
The code is:

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, StrDetails As String
With ContentControl
  If .Title = "Client" Then
  For i = 1 To .DropdownListEntries.Count
    If .DropdownListEntries(i).Text = .Range.Text Then
      StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11))
      Exit For
    End If
  Next
  ActiveDocument.ContentControls(2).Range.Text = StrDetails
  End If
End With
End Sub

To access the macro in the attached document, open it and press Alt-F11.

rich_cirillo · #3 04-02-2013, 08:42 PM

That looks interesting...i could maybe used this for my needs

Rich

paul_pearson · #4 04-02-2013, 09:48 PM

Hi Paul
Looks excellent...i will have a play around later but so far looks great..
Is there a limit on how many entries i can place in the Content Control Dropdown box
If i want to add other lists further down the page such as personal information will i just use the code you supplied or will a further code update be required
Example:
Names: xxxxxxxxxxxx
Credit Card No: xxxxxxxxx
Card type: xxxxxxxxxx
Expiry date: xxxxxxxxxx
Thanks
Paul

macropod · #5 04-02-2013, 11:02 PM

Quote:

Originally Posted by paul_pearson

Is there a limit on how many entries i can place in the Content Control Dropdown box
If i want to add other lists further down the page such as personal information will i just use the code you supplied or will a further code update be required

I recently set up a process for someone who wanted to add 600 entries to one. Is that enough?

You won't need to make any code changes. Simply add your entries with the names as the content control display name and the other details with pipe separators (ie |) for each line as the content control value. It doesn't even matter if the number of lines vary from one client to the next.

paul_pearson · #6 04-02-2013, 11:12 PM

Hi Paul

Thank you

I think i will have 50

If you have time could you please attach a sample of the "other details with pipe separators (ie |) for each line as the content control value. It doesn't even matter if the number of lines vary from one client to the next"

Would be appreciated

Paul

macropod · #7 04-02-2013, 11:18 PM

Quote:

Originally Posted by paul_pearson

If you have time could you please attach a sample of the "other details with pipe separators (ie |) for each line as the content control value. It doesn't even matter if the number of lines vary from one client to the next"

You can already see the use of the pipe separators in the example I posted. Simply click on the content control, choose 'properties' (from the Ribbon's Developer tab), then select one of the entries and choose 'Modify'. If you add another pipe separator, plus some text, or delete everything from the existing last pipie separator onwards, you'll end up with a difference in the number of output lines for the two records you now have.

Dludwikowski · #8 03-12-2014, 08:35 AM

Quote:

Originally Posted by macropod

You could add the entries to a dropdown content control. As the dropdown only supports a single line per entry, the other data could be output to a plain text or rich text content control via an on_exit macro. See attached demo

Exactly what I want to do as well. Is it possible to do it for multiple content box dropdowns? I have a few different drop down fields that I want to change to the 'value', but when I test it out, it keeps only changing the first one.

Thanks!

macropod · #9 03-12-2014, 02:14 PM

Yes, that's quite possible. You could, for example, keep adding If ... End If tests to the code for each of the additional content controls. As for the output, you need to make sure the 2 in 'ActiveDocument.ContentControls(2).Range.Text' refers to the correct content control in each case (e.g. you might need to change it to 4 for the second one, and so on).

opencollar · #10 05-20-2014, 10:24 AM

Thanks Marcopod for your zip attachment. I've been following this thread and being new to VBA, I'm trying to figure out where in the macro I type in what text to display.

E.g., what i need is for someone to be abe to see full words in a dropdown list, but then display a Code corresponding to each word. I could have the code included in the dropdown with the word, then possibly only set it to show the first x characters (to only display the code). Any way to just display the code, and not the full words would be helpful. The macro looks like its what I need, but I cant figure out what text goes where.

I will have 4 separate dropdown lists with each list repeating down four to five lines.

Thanks,

macropod · #11 05-20-2014, 02:17 PM

The values to display have been input into the first content control. That content control has both a Display text and a Value. The conditional outputs are stored in the value field, with | delimiters for each output line.

vincente · #12 08-07-2014, 05:23 AM

this is exactly what i'm looking for... But i know nothing about vba/macro's... can anybody quickly explain to me how to link the drop-down list to the text box?
I've tried copy-pasteing macropod's example, but that doesn't seem to work...

cheers

macropod · #13 08-07-2014, 03:10 PM

There is no textbox in the attachment demo, just two content controls and the macro that does the updating.

vincente · #14 08-08-2014, 02:16 AM

ok, but the problem is that this is all new to me and i don't have a clue how to make it myself.

could you help me with this?

macropod · #15 08-08-2014, 04:26 AM

Open the document and click on the ¶ symbol on the Ribbon's Home tab to display the document's formatting. You should now be able to see that there are two content controls, separated by a manual line break, with a paragraph break following the second one. Click on the Ribbon's Developer tab, then on 'Design Mode'. This should make the content controls even more apparent and will reveal their titles - Client and ClientDetails, respectively.

To use these controls as-is in your own document, simply copy both of them, plus the macro code to your document. To change the Client dropdown's contents, click on it, then on 'Properties' on the Developer tab. There you can add/delete/modify the entries. Pay particular attention to how the DisplayName and Value are input, especially the latter.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete Multiple Entries	dudeabides	Office	1	07-04-2011 02:49 AM
Multiple task lists and multiple calendars	kballing	Outlook	0	01-18-2011 10:23 AM
Creating Multiple Contact Lists	meltee78	Outlook	1	01-03-2011 09:45 PM
multiple calendar entries across a group	halfhearted	Outlook	0	10-11-2009 12:13 PM
Word Forms : Dropdown lists	wferaera45	Word	0	04-06-2006 03:02 AM