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]

@Bagsy
1. Do you apply the data validation + combobox in a single cell or multiple cell?
2. Do you want to show the combobox by double-clicking the cell?

Actually you can have a searchable combobox without data validation, I have a macro to create that kind of combobox. Please check this link (and read post #109):
Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Let me know if you're interested in this approach.

 

[Bagsy]

Hi Akuini,

Thanks for offering your help
I clicked on that link and it just took me to a web page called media fire with a zipped file, which I wasn’t keen on trying to open.

What I would really like to achieve is do away with the data validation and have a searchable combo box” on page1 of my workbook “Purchase order List”, linked to my named range as below so it adds the result of the search to the next empty cell in column D7:D something like Range D7:D xlup.Offset(1, 0)

My spreadsheet is as below
Column A = Empty (not used)
Column B = unique PO number
Column C =date
Column D = supplier
Then E’F’G & H hold other information, costs etc.

I am open to any suggestions, but it needs to be dynamic as the approved suppliers list is in a table because suppliers get added and removed, which in turn updates my named range automatically.

Many thanks Gary

 

[Akuini]

I clicked on that link and it just took me to a web page called media fire with a zipped file, which I wasn’t keen on trying to open.

The zip file contains an xlsm file. The reason I put it in a zip file is because if I upload it as xlsm file then you download it via Firefox browser then the extension somehow changes to xlsx. But I understand your hesitation to open it.

The searchable combobox I'm talking about is described in post #2 in that thread:
Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

but it needs to be dynamic

The code makes the combobox list dynamic, sorted, & unique.

What I would really like to achieve is do away with the data validation and have a searchable combo box ...

but it looks like you want to use data validation + combobox, so I can't help you with that.

 

[Bagsy]

Hi Akuini
I'm not an expert in any of this and initially was using data validation to get going with a view to get a combo box soulution at a later date. I found the code that worked with data validation and was giving it a try but it is not what i really wanted, I just want a combo box solution. I will take a look at your code to see if I can get it to work for me.
Thanks for your help it is really appreciated.

 

[Bagsy]

Akuini
That is brilliant, thank you so much for your help
Gary

 

[Akuini]

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

 

[Bagsy]

Hi Akuini
Sorry to bother you again, is there anyway I can keep the combo box on, so its ready to use each time you open the workbook. At the moment it switches to off when I shut down. I would like it default to on until I switch it off with the toggle switch.

Thanks Gary

 

[Akuini]

is there anyway I can keep the combo box on, so its ready to use each time you open the workbook

Try this:
1. Remove this line from sheet1 module:

Private xFlag As Boolean

2. Insert a module and then put this line:

Public xFlag As Boolean

3. In ThisWorkbook module, put this line:

Private Sub Workbook_Open()
xFlag = True
End Sub

 

[Bagsy]

Thank you so much for your time.