VBA coding for multiple drop-down lists to populate a text box for each list

yeatropulo · #1 09-29-2015, 09:11 AM

Can anybody help... I'm trying to create a word document well an order form actually with lines of products each of which can have several variants with a different value hence the drop-down list. I want to be able for the user on choosing the product's variant to see it's value in the adjacent text box/field. My aim is to have a final shopping list as it were and on each line a value with a total at the bottom. I have figured out the maths bit and am able to total the numbers at the end, what I can't manage is to create more than one drop-down list and auto text box. Hope that makes sense and someone is able to help.

This is the code I'm using to populate the text box with a value.

Option Explicit

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, StrDetails As String
With ContentControl
If .Title = "Choice1" 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

macropod · #2 09-29-2015, 02:16 PM

That code looks suspiciously like some I've posted here...

To work with multiple content controls, you could tag each of the output ones to match the titles of the ones you're using for the inputs, then use code like:

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, StrDetails As String
With ContentControl
  Select Case .Title
    Case Is = "Choice1"
      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.SelectContentControlsByTag("Choice1").Item(1).Range.Text = StrDetails
    Case Is = "Choice2"
      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.SelectContentControlsByTag("Choice2").Item(1).Range.Text = StrDetails
    Case Else
  End Select
End With
End Sub

Note: You would really only need:
StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11))
if the output has multiple lines; otherwise you could use just:
StrDetails = .DropdownListEntries(i).Value

yeatropulo · #3 09-30-2015, 12:51 AM

Sorry... your suspicions are correct, being new to all this and out of desperation I may have inadvertently copied the code from another post you answered. Thank you for your quick reply I will give it a go and let you know.

macropod · #4 09-30-2015, 01:25 AM

Quote:

Originally Posted by yeatropulo

out of desperation I may have inadvertently copied the code from another post you answered.

That's quite OK. Anyone posting code on websites should expect others might make use of it.

yeatropulo · #5 10-07-2015, 06:56 AM

Hi Paul, I'm back again.... I've tried to run the code you very kindly posted last week and I'm sure it's something I'm doing but I keep getting an error and when I select debug it highlights line of code. As I'm pretty new to all this do you have an example of this code in action that I can down load and interrogate. Thank you once again for your help.
Regards

Mark.

macropod · #6 10-07-2015, 02:55 PM

Which line of code is highlighted and what is the error message?

yeatropulo · #7 10-08-2015, 01:31 AM

Hi Paul,

Thank you for getting back to me, I seem to be getting more than one error message so I've attached the document in question that way you can see for yourself and as I said I'm sure it's down to my lack of knowledge and experience which is the cause of all my woes. Thank you once again for you help.

Mark.

macropod · #8 10-08-2015, 01:56 AM

The problem is that you've given each pair of content controls the same title and tag. The dropdown should have only the title and its dependent should have only the tag.

yeatropulo · #9 10-08-2015, 02:28 AM

Brilliant!! You are a genius , now all I need to do is get a book and learn this stuff myself and stop bothering you. Thanks again.

Mark.

yeatropulo · #10 11-10-2015, 05:37 AM

Hi Paul, I've finally finished my automated form with multiple drop down lists that populate an adjacent text box with a value of the selected item, creating a type of shopping list that then gives a final total. The issue I have now is that having saved the form with Editing restrictions of No changes (Read only) but also with Exceptions, all of which work perfectly on my machine but when I send them to a colleague to try out the functionality is lost. The drop down lists work but the text box don't update with a value. I've allowed macros on my colleagues machine but still no joy. Any help would be greatly received. Mark.

macropod · #11 11-10-2015, 01:59 PM

Are you sure the macros are in the document you're sending - and not in its template?

yeatropulo · #12 11-11-2015, 02:56 AM

Thanks for getting back to me, I'm fairly sure, I saved the document as a macro enabled file assuming it would create the macros automatically, is that not the case? Have I wasted my time? Should I have done something crucial from the outset?

macropod · #13 11-11-2015, 03:01 AM

That doesn't affect which file the macros are in - only whether the file is capable of containing macros (docx files cannot). That said, your previous screen shots do seem to indicate the macro was being saved in the file containing the content controls, not in separate template.

yeatropulo · #14 11-11-2015, 05:16 AM

As you can see from the attached I saved the file as a .docm

macropod · #15 11-11-2015, 01:08 PM

OK, the macro is in the document, so it should work for your anyone using the document unless the macro is disabled - whether due to security restrictions on your colleague's computer, VBA not being installed on it, etc. That said, your Line totals never update and there doesn't seem to be any code for that.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data	Faldinio	Word VBA	7	10-19-2014 06:03 AM
Link multiple cells in drop-down lists	Trial4life	Excel	0	09-08-2014 05:08 AM
Multiple Choice Drop Down List	teza2k06	Word	1	04-29-2014 04:54 PM
Multiple Drop Down Lists in word	ali212006	Word	3	03-13-2013 12:16 PM
Using both drop down lists and blank cells for text	voltarei	Word	0	03-18-2010 04:16 AM