Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-02-2013, 06:49 AM
paul_pearson paul_pearson is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 64bit
Novice
Multiple entries in dropdown lists
 
Join Date: Mar 2013
Posts: 5
paul_pearson is on a distinguished road
Default Multiple entries in dropdown lists

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
Reply With Quote
  #2  
Old 04-02-2013, 08:16 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
Attached Files
File Type: zip Content Controls - Dropdown Dependent Text.zip (32.0 KB, 3209 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-02-2013, 08:42 PM
rich_cirillo rich_cirillo is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 64bit
Novice
 
Join Date: Sep 2012
Posts: 27
rich_cirillo is on a distinguished road
Default

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

Rich
Reply With Quote
  #4  
Old 04-02-2013, 09:48 PM
paul_pearson paul_pearson is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 64bit
Novice
Multiple entries in dropdown lists
 
Join Date: Mar 2013
Posts: 5
paul_pearson is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 04-02-2013, 11:02 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by paul_pearson View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 04-02-2013, 11:12 PM
paul_pearson paul_pearson is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 64bit
Novice
Multiple entries in dropdown lists
 
Join Date: Mar 2013
Posts: 5
paul_pearson is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 04-02-2013, 11:18 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by paul_pearson View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 03-12-2014, 08:35 AM
Dludwikowski Dludwikowski is offline Multiple entries in dropdown lists Windows XP Multiple entries in dropdown lists Office 2010 32bit
Novice
 
Join Date: Mar 2014
Posts: 1
Dludwikowski is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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!

Last edited by Dludwikowski; 03-12-2014 at 09:55 AM.
Reply With Quote
  #9  
Old 03-12-2014, 02:14 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 05-20-2014, 10:24 AM
opencollar opencollar is offline Multiple entries in dropdown lists Windows 7 64bit Multiple entries in dropdown lists Office 2010 64bit
Novice
 
Join Date: May 2014
Posts: 1
opencollar is on a distinguished road
Default Macros for drop down list

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,
Reply With Quote
  #11  
Old 05-20-2014, 02:17 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 08-07-2014, 05:23 AM
vincente vincente is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Novice
 
Join Date: Aug 2014
Posts: 4
vincente is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 08-07-2014, 03:10 PM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

There is no textbox in the attachment demo, just two content controls and the macro that does the updating.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 08-08-2014, 02:16 AM
vincente vincente is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Novice
 
Join Date: Aug 2014
Posts: 4
vincente is on a distinguished road
Default

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?
Reply With Quote
  #15  
Old 08-08-2014, 04:26 AM
macropod's Avatar
macropod macropod is offline Multiple entries in dropdown lists Windows 7 32bit Multiple entries in dropdown lists Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple entries in dropdown lists 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:52 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft