How to solve a fill range need in a combo box within a VBA code?

[emptiness_void]

Hello, hope everything is good!

I will try to be the most clear with the goal I'm tryin to achieve:

I have a Sheet1 where I would like to enter a given record with some information about Country; Transit Type; Incoterm; Forwarder; Currency info.



This would be almost what I needed (it shouldn't show blanks, and that happens because on the data validation source I wrote a range for the A column in Sheet2), a combo box that appears on the cell I want to enter the data with search option and a auto complete option!

The validation info. is on Sheet2


I'm only testing for the country column for now this following code where I found and edited on Internet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim r As Range
    Set ws = ActiveSheet

      On Error GoTo errHandler

    If Target.Count > 1 Then GoTo exitHandler

      Set cboTemp = ws.OLEObjects("TempCombo")
        On Error Resume Next
      If cboTemp.Visible = True Then
        With cboTemp
          .Top = 10
          .Left = 10
          .ListFillRange = ""
          .LinkedCell = ""
          .Visible = False
          .Value = ""
        End With
      End If

      On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
    '  value 3 to confirm there is the data validation
        Application.EnableEvents = False

        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)


        With cboTemp
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 15
          .Height = Target.Height + 5
'--------- this is where I think the code doesn't work as I would want, doesn't recognize a dynamic range for example!
          .ListFillRange = str
'-----------------
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
      End If

exitHandler:
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      Exit Sub
errHandler:
      Resume exitHandler

    End Sub

1. The images shown are for a cell from Sheet1 with a data validation source with reference to Sheet2!A:A (although I don't want this solution because it gives me many blanks and the header), it's a Plan B if I can't find anything better :/
2. I tried to use the "=OFFSET(Sheet2!A1:A57;1;0;COUNTA(Sheet2!$A:$A)-1;1)" as the source in data validation cells in Sheet1 but the code fails to recognize this as a range!
   1. This source formula (offset) in my point of view would be the "best" way to do it, because it takes in account new records added in the Sheet2 that someone can add, and it would consider them dynamically!

The code seems good in general, because it would allow me also to replicate the "scenario" for transit type, and the others columns, just by adding data validation options to those cells! I aiming to a solution similar to this because it seems more automated and wouldn't need to write code for different columns in Sheet1, I think.

I'm a beginner in vba coding in excel, and I'm trying to learn the best I can but it has been hard, especially because I don't have anyone on my team to share this questions!


If you have any questions or need more information, please contact me!

Thank you if you have read till this far and I hope you can help me!

 

[emptiness_void]

Can someone help me, please?

 

[Akuini]

Hi, @emptiness_void
Welcome to the forum:
See if this example of a searchable combobox suit your need:

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Hi Folks, I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee". Time-sheet worksheet. Employee Start time Finish time Hours worked Joe Blogs Jane Doe I wish to be able to start typing a different employee on each row...

www.mrexcel.com

If you're interested in this method I think I can modify the code to apply the combobox on multiple columns (you need it in col A & B?)

 

[emptiness_void]

Hi, @Akuini :D

Thank you very much for this file you shared!
It helped me a lot it and yes it's even better than the solution I was testing and looking for!



Yes i'm interested!! The next step would be to modify the code (the beginning of it? idk) so I do this in multiple columns! Using the combo box defined to H column also, for example, and other column in Sheet1!

I have 6 columns in Sheet2 that store data to be searched in the combo box when selecting different columns in Sheet1!

Is there a way to do this? If you can helped me with this part I would be very grateful!

 

[Akuini]

Ok, first you need to download the example that uses the combobox in single column.

deCombobox-visible,searchable,unique,sort,arraylist,on-off-04

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

I modified the code in the above example to show how you can apply the combobox on 3 columns (col B,D,E).

deCombobox-visible,searchable,unique,sort,arraylist,multi coulmn,on-off-04

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

To apply the combobox on different columns & different number of column, you can change the code in this part:
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================