You have already been given the solution to this requirement practically on a silver platter. If you are then going to move the goal post after the start of the game at least take the time to read and try to apply it.
Basically you need to change your OnEntry procedure as such:
Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl)
Dim lngIndex As Long
Dim strSQL As String
Select Case oCC.Title
Case "Client"
oCC.DropdownListEntries.Clear
strSQL = "SELECT * FROM [Sheet1$];"
xlFillList arrData, ThisDocument.Path & "\Providers.xlsx", "True", strSQL
For lngIndex = 0 To UBound(arrData, 2)
'Since you have a gazillion empty records in your excel file:
If Not IsNull(arrData(0, lngIndex) Then
oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex)
End If
Next lngIndex
End Select
End Sub
The rest of the code would be edited accordingly (e.g., the OnExit) and all the rest of the CCs should be text CCs.
|