I am new to vba and have been working on a problem for 2 days and cannot figure it out.
I have a simple spreadsheet where the user enters a rep number and the combobox is suppose to be filled with their customer number and name.
I use MSQuery to pull in the customer number and name when the rep changes...actually I do a refresh in vba to make sure that the information is updated.
I have also copied and pasted the dynamic named range (created refrencing the table name...=Table_Name) to another sheet to make sure that it is pulling the correct data.
The problem I am having is the combobox is not always updated with the correct number of customers.
The first rep that comes in when opening the worksheet has 13 different customer...works great.
I change the rep to one that has 7 customers and it displays but with blank lines at the end of the combobox dropdown. Sheet 2 has the correct customers in it (this is my copy and paste of the dynamica named range).
I now change the rep to one that has 322 customers and the combobox is only displaying the first 7. It is like the combobox is one step behind the refresh in just the length of the combobox...the data is good what is displayed.
My dynamic named range is correct because sheet2 where it is copied into is correct. It just seems the combobox is not picking up the correct number of entries in the dynamic named range.

What am I doing wrong.
The ListFillRange on my activex combobox is test...where test refers to =Table_Name from my MSQuery.

Not sure what you need to see but I have included the vba code that runs when a change to A1. The only changes to the combobox properties that I made were the column Count, column wideths, linked Cell an ListFillRange.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("$A$1:$A$1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

MsgBox "Cell " & Target.Address & " has changed."
Range("Table_Query_from_bfd111s0007[#Headers]").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheets("Sheet2").Range("A:C").Clear
Sheets("Sheet1").Range("test").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
End If

End Sub