Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-07-2020, 02:22 AM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default Can Select Case be used to defined the Value of an item in a drop down list?

Hi folks,

I've been piecing together some code from a lot of the helpful resources on here as I'm trying to make more efficient form templates for the company I've recently joined. So far I have a drop down CC from which the Team Leader for any given site visit can be picked. Once this is chosen, their respective mobile number populates a Rich Text CC adjacent (In the drop down properties, TL name is set as Display Name, mobile number is set as Value). Additionally, I currently have, per the code below, a way that the specific Senior Operative(s) for each Team Leader populate a second drop down CC below (this is important as there will be a large quantity of Senior Ops in the finished code, saves people scrolling through them all).

Code:
With CCtrl
  If .Title = "TLName" Then
    If StrOption = .Range.Text Then Exit Sub
    Select Case .Range.Text
      Case "Alan B"
        StrOut = "Kenneth J"
      Case "Alan T"
        StrOut = "Colin L"
      Case Else
        .Type = wdContentControlText
        .Range.Text = ""
        .Type = wdContentControlDropdownList
    End Select
    With ActiveDocument.SelectContentControlsByTitle("SOName")(1)
      .DropdownListEntries.Clear
      For i = 0 To UBound(Split(StrOut, ","))
        .DropdownListEntries.Add Split(StrOut, ",")(i)
      Next
      .Type = wdContentControlText
      .Range.Text = ""
      .Type = wdContentControlDropdownList
    End With
  End If
End With
My issue is that this code defines the SO name as both the Display Name and Value whereas I would like to implement the same method to retrieve the SO mobile number as I did with the Team Leaders. Is there a way the Value can be defined independently of the Display Name, or a simpler way this could be done in general? If need be, I can provide a simplified copy of the template with the code in its entirety (I omitted this as there are other parts of the code irrelevant to this query).

Any help would be greatly appreciated.

Thanks,



Michael
Reply With Quote
  #2  
Old 10-07-2020, 09:57 AM
gmaxey gmaxey is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
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

Can't make heads or tails out of what you are trying to do. Sorry.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 10-07-2020, 02:49 PM
Guessed's Avatar
Guessed Guessed is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Michael

I think you need to post a sample document containing the Content Controls you are trying to code for and some dummy list data that you want to integrate with your secondary CC.

Splitting StrOut doesn't look particularly useful when there are no separators in your input values.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 10-07-2020, 04:10 PM
Charles Kenyon Charles Kenyon is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,082
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

You may want to look at these threads:
Reply With Quote
  #5  
Old 10-07-2020, 11:52 PM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default

Appreciate the feedback so far. I've attached a sample version of just the controls that involve code. The only bit I need help with is the Client contact name row in the Contact Details box. Right now, how it works is that the Team Leader is selected which populates their mobile number to the right and their respective Senior Operative(s) in the drop down below.

The issue is that currently, when a Senior Op is selected, their name populates the mobile number field rather than their number because the code enters the Snr Op name in both Display Name and Value.

Is there a way to alter the code that it defines the Display Name and Value of a dropdown list item separately?

Many thanks.
Attached Files
File Type: dotm Sample Template.dotm (99.7 KB, 12 views)
Reply With Quote
  #6  
Old 10-08-2020, 12:57 AM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default

I've had an idea of an alternative if this isn't possible or clear. Is it possible to have the Snr Op mobile number rich text CC populate based on their name being selected in the drop down by directly quoting both the name and number in the code? It would obviously require manually coding every name and respective number but if there's an easy way to implement this I'd be happy enough. Been bashing my head at this for a day or so and I'm very unfamiliar with VBA.

Have to say, though, the information in these forums has been so helpful already and I've already been able to put things in place that should save people a lot of time so cheers to you guys.
Reply With Quote
  #7  
Old 10-08-2020, 01:06 AM
Guessed's Avatar
Guessed Guessed is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

You haven't provided the phone numbers for the SOs anywhere so this is going to be a 'concept' post rather than a pre-rolled solution. Firstly, you populate the text AND the value at the same time

.DropdownListEntries.Add Text:="Joe Blow", Value:="123456"

Now, the bigger question arises of how do provide the lookup values in a sensible layout so it is editable and expandable. Do you have any experience with custom xml? This would require a lot less coding if you had a custom xml file embedded and mapped some of the CCs to that xml data.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 10-08-2020, 01:49 AM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default

The specific numbers don't matter at the moment so anything could be assigned to the SOs. It's more to understand the concept of if and how it can be done. Even just to add single numbers for each Snr Op e.g.

Ken L - 1, Dan B - 2, Craig P - 3, and so on.

Quote:
.DropdownListEntries.Add Text:="Joe Blow", Value:="123456"
Where in the code would I implement this? I tried underneath Case "Alan J" for example and found it added Joe Blow to the Team Leader drop down list. This seems like it's on the right track but I've most likely put it in the wrong place. I have to apologise, I only started trying to use VBA two days ago but hopefully will pick things up as I go.

As for XML, unfortunately I have no experience with it. I have been looking at some code of it, for example Method F in the demo document here: Link "Conditional Content" to a List Selection

This seems along the lines of what would work and, as you say, end up a lot neater. But if your suggestion can be implemented in a way that works, the code doesn't have to be particularly pretty. I'll be the only one altering it if it needs changed anyway.

Thanks again for your help.
Reply With Quote
  #9  
Old 10-08-2020, 04:11 AM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default

I looked more into the XML side of things and actually managed to implement what I was looking for through that using code I found online. Absolutely delighted!

I have another (hopefully simpler) query now. Further down in the document is a dropdown CC for nearest hospitals and doctors to each site (this is in the sample document). My question concerns the following pieces of code:

Code:
ActiveDocument.ContentControls(10).Range.Text = StrDetails
Code:
ActiveDocument.ContentControls(12).Range.Text = StrDetails
What do I need to change these lines to so they refer to the CC Title or Tag instead of their position in the document? This is vital to me as employees may wish to remove preceding CCs if the data they contain is outdated (until I can update the code) which would cause the Hospital and MIU drop downs to stop working unless the code refers to them in a non-relative manner.

Kind regards,
Michael
Reply With Quote
  #10  
Old 10-08-2020, 04:22 AM
Guessed's Avatar
Guessed Guessed is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Great, did you find how to link both the dropdown and plain text CC to the same xml node so that the value displays in the plain text CC and reflects the value of the dropdown CC entry? This avoids the need for vba to connect the two. You will still need vba for the dependent list of Snr Ops but it does simplify things.

Getting the CC by its Title or Tag is
Code:
ActiveDocument.SelectContentControlsByTitle("TLName")(1).Range.Text = strDetails

'You can also do it by Tag
ActiveDocument.SelectContentControlsByTag("TLName")(1).Range.Text = strDetails
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #11  
Old 10-08-2020, 05:04 AM
mbews mbews is offline Can Select Case be used to defined the Value of an item in a drop down list? Windows 10 Can Select Case be used to defined the Value of an item in a drop down list? Office 2019
Novice
Can Select Case be used to defined the Value of an item in a drop down list?
 
Join Date: Oct 2020
Posts: 6
mbews is on a distinguished road
Default

Quote:
Great, did you find how to link both the dropdown and plain text CC to the same xml node so that the value displays in the plain text CC and reflects the value of the dropdown CC entry?
I did indeed! Well.. the code had it like that at least haha.

Thank you so much, that works perfectly. You've made my day!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto create new worksheet when select option from drop down list JANET JONG Excel Programming 1 08-01-2018 07:13 AM
Select multiple items from a drop down list ConfuddledOne Excel Programming 0 01-30-2017 08:13 PM
Returning a specific value when item is selected from a drop-down list J Press Excel 4 09-10-2012 06:12 AM
Show the first item of a drop-down list instead of a blank Pragabhava Excel 1 12-29-2011 11:48 PM
Can Select Case be used to defined the Value of an item in a drop down list? Automatically select first item in drop-down? flackend Excel 4 08-29-2011 02:07 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:16 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