Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Coding dropdown-list value

    I am having some issues finding how to script out a condition that works on the "value" not the text of items in a dropdown list.

    This is the code I currently have in place. The bolded lines are what I need to change. The code currently works great with the text, but I really need this to work with the applied value of the items in the control. I feel like there is a property that I just don't know about.

    Help please

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Select Case ContentControl.Tag
    Case "OrderType"
    If ContentControl.Range.Text = "Spot Buy" Then
    Bookmarks("CustPlanning").Range.Font.Hidden = True
    Bookmarks("ShipFrequency").Range.Font.Hidden = True
    Bookmarks("OrdFrequency").Range.Font.Hidden = False
    Bookmarks("OrdVisibility").Range.Font.Hidden = False


    ElseIf ContentControl.Range.Text = "Blanket Order" Then
    Bookmarks("OrdFrequency").Range.Font.Hidden = True
    Bookmarks("OrdVisibility").Range.Font.Hidden = True
    Bookmarks("ShipFrequency").Range.Font.Hidden = False
    Bookmarks("CustPlanning").Range.Font.Hidden = False


    ElseIf ContentControl.Range.Text = "Firm Order Scheduled Releases" Then
    Bookmarks("CustPlanning").Range.Font.Hidden = True
    Bookmarks("OrdVisibility").Range.Font.Hidden = True
    Bookmarks("ShipFrequency").Range.Font.Hidden = False
    Bookmarks("OrdFrequency").Range.Font.Hidden = False


    ElseIf ContentControl.Range.Text = "Choose an item" Then
    Bookmarks("CustPlanning").Range.Font.Hidden = False
    Bookmarks("ShipFrequency").Range.Font.Hidden = False
    Bookmarks("OrdFrequency").Range.Font.Hidden = False
    Bookmarks("OrdVisibility").Range.Font.Hidden = False
    End If

    End Select


    End Sub

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,494
    Thanks
    3
    Thanked 139 Times in 132 Posts
    I'll tidy your code first because (in my opinion), you should have put the Select inside the If instead of the other way around.
    Code:
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    
    If ContentControl.Tag = "OrderType" then
      Bookmarks("CustPlanning").Range.Font.Hidden = False
      Bookmarks("ShipFrequency").Range.Font.Hidden = False
      Bookmarks("OrdFrequency").Range.Font.Hidden = False
      Bookmarks("OrdVisibility").Range.Font.Hidden = False
    
      Select Case ContentControl.Range.Text
              Case "Spot Buy"
                   Bookmarks("CustPlanning").Range.Font.Hidden = True
                   Bookmarks("ShipFrequency").Range.Font.Hidden = True
              Case "Blanket Order"
                   Bookmarks("OrdFrequency").Range.Font.Hidden = True
                   Bookmarks("OrdVisibility").Range.Font.Hidden = True
              Case  "Firm Order Scheduled Releases"
                  Bookmarks("CustPlanning").Range.Font.Hidden = True
                  Bookmarks("OrdVisibility").Range.Font.Hidden = True
             End Select
      End If
    
    End Sub
    Now what did you want to find the value of? If it is the text inside the content control then I think you already have it.
    Last edited by RetiredGeek; 2013-08-13 at 08:19. Reason: Added Code tags
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    jpearce (2013-08-14)

  5. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the reply Andrew,

    I have assigned values to the items in the dropdown list (shown below). I want the code to work off the value instead of the displayed text because this is going to be distributed to different countries for translation into other languages. I'm not too keen on having non-technical users of this document attempt to edit the code. Especially now that you've cleaned it up so nicely.

    Capture.PNG

  6. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,671
    Thanks
    49
    Thanked 61 Times in 59 Posts
    I think you are going to have to add an intermediate test to get your values. It would probably help if you put the value number at the beginning of your selections. Then you could parse the text.
    Charles Kyle Kenyon
    Madison, Wisconsin

  7. #5
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    It works, its just not pretty

    I like the idea of putting the number value at the beginning of the selection, it technically works very well. However, it does create an aesthetic issue of having one numbered field in the midst of un-numbered fields.

    I feel like I'm going to have to find some other work around for this, like manually changing all the translated copies. We'll see...

    I'm still curious what the point of the "value" is with a drop down list (example below). If someone could answer that and sate my curiosity I think that would make me very happy at this point.
    HighlightedCapture.PNG

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,042
    Thanks
    195
    Thanked 755 Times in 691 Posts
    J.

    Could you possibly post a sample file that we could play with?
    I have an idea but need something to try it out on.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #7
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Here's the relevant page,

    The code all works based on the Order Type list

    have fun with it!


    online distribution proposal.docm

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,042
    Thanks
    195
    Thanked 755 Times in 691 Posts
    J.

    Thanks for posting the file. Unfortunately, the idea I had didn't pan out. I was looking at the Microsoft Forms BoundColumn property but there is no way to get at this in a Content Control. Good Luck in your quest.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #9
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,494
    Thanks
    3
    Thanked 139 Times in 132 Posts
    There doesn't appear to be an index property for the dropdown CCs so it appears your code needs to loop through the count to find the index and hence obtain the value.
    See http://vbadud.blogspot.com.au/2010/0...m-content.html

    Let us know if you can't work out how to adapt that code to your specific problem. It can be done but it doesn't look like the MS propellor heads thought we would want to do this with their lovely Content Controls.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. #10
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,671
    Thanks
    49
    Thanked 61 Times in 59 Posts
    Here is a programmer's reference I found in my search, that did not have the answer but may be of use to someone else who stumbles on this thread.

    http://msdn.microsoft.com/en-us/libr...ntcontrol.aspx
    Charles Kyle Kenyon
    Madison, Wisconsin

  13. The Following User Says Thank You to Charles Kenyon For This Useful Post:

    jpearce (2013-08-14)

  14. #11
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Madison, Wisconsin, Wisconsin, USA
    Posts
    1,671
    Thanks
    49
    Thanked 61 Times in 59 Posts
    I can't for the life of me figure out what the "value" setting is for. I would actually assume that it would be for just what you are trying to do.

    Why does the control even have this if you can't use it?
    Charles Kyle Kenyon
    Madison, Wisconsin

  15. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    195
    Thanks
    4
    Thanked 45 Times in 37 Posts
    Here's a general-purpose function that will loop through the items in the list until it finds the list item that matches the user's selection, and then returns the .Value corresponding to that entry. Call the function and pass in the content control that you want to test.

    Code:
    Private Function GetValue(ByVal CC As ContentControl) As String
        Dim entry As ContentControlListEntry
        Dim rtn As String  ' defaults to ""
        
        If CC.Type = wdContentControlDropdownList Then
            For Each entry In CC.DropdownListEntries
              ' If Not CC.ShowingPlaceholderText Then   ' if the placeholder has a Value, uncomment this and the End If
                    If CC.Range.Text = entry.Text Then
                        rtn = entry.Value
                        Exit For
                    End If
              ' End If
            Next
        End If
        
        GetValue = rtn
    End Function
    Working from Andrew's rewrite of your code,

    Code:
     Select Case GetValue(ContentControl)
              Case "2"
                  ...
              Case "3"
                  ...
              Case "4"
                  ...
    Last edited by jjfreedman; 2013-08-13 at 19:13.

  16. The Following User Says Thank You to jjfreedman For This Useful Post:

    jpearce (2013-08-14)

  17. #13
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,494
    Thanks
    3
    Thanked 139 Times in 132 Posts
    Charles/jpearce

    The fact that the dropdown list has a separate values column shows that Microsoft programmers thought it could come in handy at some stage. The problem is that the coders who added that feature didn't/couldn't implement the next step in Word 2010. Perhaps in the three years til the release of v2013 the coders may have noticed the omission and may have added this to the 2013 vba object model. On the other hand, it may have been something that completely fell through the cracks and has been forgotten about.

    An example where it could be very useful is demonstrated by jpearce's form. It would be a lot easier to localise different versions of the form if the necessary language changes could be made to the content control only instead of having to do that to the code as well.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  18. #14
    New Lounger
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    JJ
    Your code did the trick, this seems to be working perfectly.

    A big thanks to everyone for their ideas and efforts on this!

    This is my first time ever posting to a forum like this and its been a wonderful experience!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •