Combo box linked to a named range as a table

[Bagsy]

I hope somebody can help
On my workbook I have data validation linked to a named range called MyApprovedSuppliers.
This refers to a table named range called ApprovedSuppliers on workbook called “Approved Suppliers List

When I open my workbook called “Purchase order list” it automatically opens workbook “Approved suppliers” and the data validation works just great.

So I can add a new P/O number to my sheet and the select the approved supplier from the dropdown list. The problem is this is a long list and I would like to create searchable combo boxes, but it doesn’t like data in a table and just shows blank.

Any help will be very much appreciated This is the code I have

Private Sub Worksheet_BeforeDoubleClick _
  (ByVal Target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains
      'a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.ComboBox1.DropDown
  End If
 
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub ComboBox1_LostFocus()
  With Me.ComboBox1
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
 '====================================

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.