Combo box linked to a named range as a table

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows
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
Capture named range.PNG

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
VBA Code:
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
 '====================================
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,308
Office Version
  1. 365
Platform
  1. Windows

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows
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)

Capture named range.PNG


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

Well-known Member
Joined
Feb 1, 2016
Messages
3,308
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows
Akuini
That is brilliant, thank you so much for your help
Gary
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,308
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,308
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Private xFlag As Boolean

2. Insert a module and then put this line:
VBA Code:
Public xFlag As Boolean

3. In ThisWorkbook module, put this line:
VBA Code:
Private Sub Workbook_Open()
xFlag = True
End Sub
 
Solution

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
409
Office Version
  1. 365
Platform
  1. Windows
Thank you so much for your time.
 

Forum statistics

Threads
1,141,576
Messages
5,707,184
Members
421,497
Latest member
philipg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top