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

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.

You sir, are a legend! :bow:

THANK YOU SO MUCH!

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. :(

Code:

---

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

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:

Code:

---

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

---

Attachment 23964

Quote:

---

Originally Posted by gmaxey

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!

Quote:

---

Originally Posted by SteveHale

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?:dunno