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

emptiness_void

New Member
Joined
Mar 29, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.

2021-03-29 12_40_34-solution1 - Excel.png

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
2021-03-29 12_41_05-solution1 - Excel.png

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

VBA Code:
    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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Hi, @emptiness_void
Welcome to the forum:
See if this example of a searchable combobox suit your need:

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

New Member
Joined
Mar 29, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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!

itsWORKING.png

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Ok, first you need to download the example that uses the combobox in single column.


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

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: ===================================
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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