Populate an ActiveX combo box with dynamic data from a different sheet

[brendalpzm]

Hello everyone,

I have this issue that I've been struguling with for 2 days.

I have an activex combo box called EmpresaCB which is located in a sheet called "Registro", the thing is that this combo box has to be populated with information that is located in a different sheet called "DB"

This information is dynamic since it changes according to a filter/unique formula everytime someone inputs data in a raw data base. This information starts from the cell A2 in the "DB" sheet.

I have a VBA code that works well if the data base is in the same sheet, this is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, Temp As String
Temp = EmpresaCB.Value
EmpresaCB.Clear
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
        If c.Value <> vbNullString Then EmpresaCB.AddItem c.Value
    Next c
EmpresaCB.Value = Temp
End Sub

The thing is that... the data base is in a different sheet, so I want to know what should I add to this code in order for it to work.

If you can help me... thanks...

 

[6StringJazzer]

I don't see why you are using Selection_Change. This will rebuild the combobox every time the user selects any cell, regardless of whether anything on the sheet changed, and this code does not even care what cell is selected. That's a lot of thrashing.

To do what you described I would create a dynamic named range on sheet DB. For example

=OFFSET(A2,0,0,1,COUNT(A:A))

The exact formula will depend on what your data looks like, but this is the idea.

Then use that name for the ListFillRange property of the combobox, and not use VBA at all.

 

[brendalpzm]

I don't see why you are using Selection_Change. This will rebuild the combobox every time the user selects any cell, regardless of whether anything on the sheet changed, and this code does not even care what cell is selected. That's a lot of thrashing.

To do what you described I would create a dynamic named range on sheet DB. For example

=OFFSET(A2,0,0,1,COUNT(A:A))

The exact formula will depend on what your data looks like, but this is the idea.

Then use that name for the ListFillRange property of the combobox, and not use VBA at all.

Thanks for replying, maybe I didn't explain myself properly, let me break it down a little bit more detailed.

I have 2 sheets,

Registro = here is where the combo box is located

DB = here is the information which the combo box will be populated with. The information starts at cell A2. The infomation could change because there is a formula that brings up unique values in a raw data base and also filters them according to some criteria, but this list of values might decrease or increase, because we can add or remove some values.

So.. the combo box changes dynamically whether we add or remove values, it also ignore blanks if that's the case.

The code works well when the information I will populate the combo box with is in the same sheet as the combo box, but I want to have the information in a different sheet than the combo box.

 

[6StringJazzer]

I understood your first explanation perfectly. My comments still apply.

Your code clears and rebuilds your combobox any time the user merely selects a cell on the sheet containing the combobox. There is no need to rebuild the combobox unless the data changes.

My solution would be to define a dynamic named range on sheet DB and use that named range for the combobox rather than using VBA.

 

[brendalpzm]

I understood your first explanation perfectly. My comments still apply.

Your code clears and rebuilds your combobox any time the user merely selects a cell on the sheet containing the combobox. There is no need to rebuild the combobox unless the data changes.

My solution would be to define a dynamic named range on sheet DB and use that named range for the combobox rather than using VBA.

The data does change,

I tried your solution but it brings up a combo box with only blank spaces, I don't know if I'm doing something wrong

 

[6StringJazzer]

Hard for me to provide greater detail or troubleshooting without being able to see your file.