Consulting

Results 1 to 7 of 7

Thread: Self-Updating ActiveX Combo Box

  1. #1

    Self-Updating ActiveX Combo Box

    Hi all.

    I am really stuck with this one for days now and desperately need assistance.
    I have exhausted every possible search term I could think of.

    I have included a sample file.

    What this document should be doing is that whenever a Zip Code is entered, the dropdown “Site” will be populated with its corresponding Site.

    In addition, to prevent incomplete forms, entering a blank response for Zip Code or Site, should popup a message indicating this and would like to SetFocus on the missing field.

    What’s happening, is that when a zipcode is left alone, it just advances and when a zipcode is entered, I don’t know how it’s picking up different values. :-(

    Any help is greatly appreciated!

    -Steve
    Attached Files Attached Files

  2. #2
    Frankly I would use content controls rather than active X controls and I would use a list for the zips and fill the site based on the zip as in the attached.You may find http://www.gmayor.com/insert_content_control_addin.htm useful for adding or editing controls.
    See attached for code examples.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    You sir, are a legend!

    THANK YOU SO MUCH!

  4. #4
    Hello again.

    I ran into a roadblock and discovered that there are some Centers that can service more than 1 zipcode. IN this case it's zipcode 95670. The only way i can think of this being making this work is to make the Site field a Combo Box.

    I have been researching on this and it all signs point to Cascading Combo Box. I've tried the objCC.DropdownListEntries.Add but i keep running into errors.

    Option Explicit
    
    Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)
        Select Case ContentControl.Title
            Case Is = "Site"
                Select Case ThisDocument.SelectContentControlsByTag("Zip").Item(1).Range.Text
                    Case 95815, 95833, 95834
                        ContentControl.Range.Text = "123 Center"
                    Case 95615, 95690, 95818, 95822, 95831, 95832
                        ContentControl.Range.Text = "ABC Center"
                    Case 95823, 95828, 95829
                        ContentControl.Range.Text = "XYZ Center"
                    
                    Case 95670
                        ContentControl.Range.Text = "XYZ Center"
                        ContentControl.Range.Text = "ABC Center"
                        ContentControl.Range.Text = "123 Center"
                    
                    Case Else
                        ContentControl.Range.Text = ""
                End Select
        End Select
    End Sub
    
    
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
        Select Case ContentControl.Title
            Case Is = "Zip"
                If ContentControl.ShowingPlaceholderText = True Then
                    ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = ""
                    MsgBox "Select the zip code!"
                    ContentControl.Range.Select
                Else
                    Select Case ContentControl.Range.Text
                        Case 95815, 95833, 95834
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "123 Center"
                        Case 95615, 95690, 95818, 95822, 95831, 95832
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "ABC Center"
                        Case 95823, 95828, 95829
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "XYZ Center"
                            
                        Case 95670
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "123 Center"
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "ABC Center"
                            ThisDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = "XYZ Center"
                        
                    End Select
                End If
        End Select
    End Sub
    Any assistance is very much appreciated.

    -Steve

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Steve,

    Your code didn't error here but then the result for zip 95670 probably isn't giving you what you want. It seems that you want to show the appropriate Site if any of the other zips are selected and if 95670 is selected then you want to show a list of sites. Correct?

    See attached and:

    Option Explicit
    Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl)
      Select Case oCC.Title
        Case "Site": DefineSite
      End Select
    lbl_Exit:
      Exit Sub
    End Sub
    
    Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
      Select Case oCC.Title
        Case "Zip"
          If oCC.ShowingPlaceholderText = True Then
            ActiveDocument.SelectContentControlsByTag("Site").Item(1).Range.Text = ""
            MsgBox "Select the zip code!"
            oCC.Range.Select
          Else
            DefineSite
          End If
      End Select
    End Sub
    
    Sub DefineSite()
    Dim oCC As ContentControl
    Dim lngIndex As Long
      Set oCC = ActiveDocument.SelectContentControlsByTag("Site").Item(1)
      With oCC
        .SetPlaceholderText , , "Service Center"
        Select Case ActiveDocument.SelectContentControlsByTag("Zip").Item(1).Range.Text
          Case 95815, 95833, 95834
            .Type = wdContentControlText
            .Range.Text = "123 Center"
          Case 95615, 95690, 95818, 95822, 95831, 95832
            .Type = wdContentControlText
            .Range.Text = "ABC Center"
          Case 95823, 95828, 95829
            .Type = wdContentControlText
            .Range.Text = "XYZ Center"
          Case 95670
            .Type = wdContentControlComboBox
            For lngIndex = .DropdownListEntries.Count To 2 Step -1
              .DropdownListEntries.Item(lngIndex).Delete
            Next lngIndex
            .SetPlaceholderText , , "Select or enter service center"
            .DropdownListEntries.Add "XYZ Center", "XYZ Center", 2
            .DropdownListEntries.Add "ABC Center", "ABC Center", 3
            .DropdownListEntries.Add "123 Center", "123 Center", 4
            .DropdownListEntries.Item(1).Select
          Case Else
            oCC.Range.Text = vbNullString
        End Select
      End With
    lbl_Exit:
      Exit Sub
    End Sub
    Toggle CC type.docm
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    Quote Originally Posted by gmaxey View Post
    Steve,

    Your code didn't error here but then the result for zip 95670 probably isn't giving you what you want. It seems that you want to show the appropriate Site if any of the other zips are selected and if 95670 is selected then you want to show a list of sites. Correct?
    That's exactly what it should be doing!

    You just saved me again kind Sir! Thanks so much!

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Quote Originally Posted by SteveHale View Post
    That's exactly what it should be doing!

    You just saved me again kind Sir! Thanks so much!
    So, what must one do to become legendary around here?
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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