Self-Updating ActiveX Combo Box

[SteveHale]

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

[gmayor]

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)http://www.gmayor.com/insert_content_control_addin.htm useful for adding or editing controls.
See attached for code examples.

[SteveHale]

You sir, are a legend! :bow:

THANK YOU SO MUCH!

[SteveHale]

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

[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?

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 Sub23964

[SteveHale]

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!

[gmaxey]

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