Can Select Case be used to defined the Value of an item in a drop down list?

mbews · #1 10-07-2020, 02:22 AM

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

gmaxey · #2 10-07-2020, 09:57 AM

Can't make heads or tails out of what you are trying to do. Sorry.

Guessed · #3 10-07-2020, 02:49 PM

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.

Charles Kenyon · #4 10-07-2020, 04:10 PM

You may want to look at these threads:

mbews · #5 10-07-2020, 11:52 PM

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.

mbews · #6 10-08-2020, 12:57 AM

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.

Guessed · #7 10-08-2020, 01:06 AM

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.

mbews · #8 10-08-2020, 01:49 AM

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.

mbews · #9 10-08-2020, 04:11 AM

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

Guessed · #10 10-08-2020, 04:22 AM

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

mbews · #11 10-08-2020, 05:04 AM

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!

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
Automatically select first item in drop-down?	flackend	Excel	4	08-29-2011 02:07 PM