Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-18-2013, 03:06 AM
ntropey87 ntropey87 is offline Contact details entered based on multiple drop down selections Windows 7 64bit Contact details entered based on multiple drop down selections Office 2010 64bit
Novice
Contact details entered based on multiple drop down selections
 
Join Date: Apr 2013
Posts: 2
ntropey87 is on a distinguished road
Lightbulb Contact details entered based on multiple drop down selections

Hi,

I am trying to create a letter template that gives the user an option to select their business division and branch from drop down boxes which will, in turn, pull the address and contact details into the footer of the document so that it can be used by any user in the company regardless of division or location (the company has five divisions and each division has up to fifteen branches).

Ideally the contact details would come from a spread sheet so that the information is updated easily by an administrator without advanced office experience.

Is this at all possible or am I asking a bit much?

Any and all assistance is greatly appreciated.
Reply With Quote
  #2  
Old 04-19-2013, 03:32 AM
macropod's Avatar
macropod macropod is offline Contact details entered based on multiple drop down selections Windows 7 64bit Contact details entered based on multiple drop down selections 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 really shouldn't need to do that. You should be able to define the relevant properties as part of the document templates used by each business division and branch, without the user ever needing to select them. Simply assign the appropriate values to the built-in document properties for each business division and branch, creating such additional properties as you might need. You can then use DOCPROPERTY fields wherever you need the data to appear in the document. As users move from one business division or branch to another, using the templates for the new business division or branch will automatically take care of the niceties, ensuring the data are correctly reported.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 04-20-2013, 02:48 AM
ntropey87 ntropey87 is offline Contact details entered based on multiple drop down selections Windows 7 64bit Contact details entered based on multiple drop down selections Office 2010 64bit
Novice
Contact details entered based on multiple drop down selections
 
Join Date: Apr 2013
Posts: 2
ntropey87 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
You really shouldn't need to do that. You should be able to define the relevant properties as part of the document templates used by each business division and branch, without the user ever needing to select them. Simply assign the appropriate values to the built-in document properties for each business division and branch, creating such additional properties as you might need. You can then use DOCPROPERTY fields wherever you need the data to appear in the document. As users move from one business division or branch to another, using the templates for the new business division or branch will automatically take care of the niceties, ensuring the data are correctly reported.
Hi Macropod,

Thank you for your response.

The idea of the template is that it can be used by anyone in the company regardless of division or branch. I was hoping to set it up so that on opening the template they can select their branch and location which inputs the relevant detail into the footer of the document. If possible, I don't want to have to have 5 different templates for divisions. Is this possible?
Reply With Quote
  #4  
Old 04-20-2013, 03:00 AM
macropod's Avatar
macropod macropod is offline Contact details entered based on multiple drop down selections Windows 7 64bit Contact details entered based on multiple drop down selections 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, it is possible, but it is error-prone and inefficient. Consider, for a moment what youre asking the user to do: choose one option from anything up to 75 options every time they create a new letter, when they shouldn'e need to choose anything. For an organisation with five divisions, the overhead in maintaining separate templates for each business division and branch is trivial.

Indeed, it could be managed via a single template that simply references the division/branch-specific data in an external file (eg via an INCLUDETEXT field), with a macro to unlink the data as each new letter is created. That means only the data file for each business division and branch needs to be maintained.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 04-20-2013, 11:19 AM
gmaxey gmaxey is offline Contact details entered based on multiple drop down selections Windows 7 32bit Contact details entered based on multiple drop down selections Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

I agree with Paul, but bored I thought I would provide some sample code. First you don't say what type of form or what type of control. This assumes a UserForm with two listbox controls. You basically have to populate the first with the divisions and then populate the second with branches using the change event of the first:

Code:
Private Sub lstDiv_Change()
If lstDiv.ListIndex <> -1 Then
  With lstBranch
    .Enabled = True
    .Clear
    Select Case lstDiv.Value
      Case "Operations"
        .AddItem "QM"
        .AddItem "ET"
      Case "Engineering"
        .AddItem "A"
        .AddItem "M"
        .AddItem "E"
      Case "Medical"
        .AddItem "X"
        .AddItem "Y"
        .AddItem "Z"
      Case "Dental"
        .AddItem "X"
        .AddItem "Y"
        .AddItem "Z"
      Case "Weapons"
        .AddItem "TM"
        .AddItem "FTB"
        .AddItem "MT"
    End Select
  End With
Else
  With lstBranch
    .Clear
    .Enabled = False
    .Clear
  End With
End If
  
End Sub
Private Sub UserForm_Initialize()
With Me
  With .lstDiv
    .AddItem "Operations"
    .AddItem "Engineering"
    .AddItem "Medical"
    .AddItem "Dental"
    .AddItem "Weapons"
  End With
  .lstBranch.Enabled = False
End With
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Contact details entered based on multiple drop down selections Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM
ppt slides with adjustable graphs based on drop down psrs0810 PowerPoint 0 01-30-2013 07:10 AM
Outlook, Excel or Access to mange contact details? davesp Outlook 0 01-24-2013 09:47 AM
Hide contact details AndyKC Outlook 0 01-25-2010 11:00 PM
Entering Contact details GemmaD Outlook 0 12-18-2008 06:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:07 AM.


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