If you need to fill or load data in a ComboBox with a table from vector in Excel, most likely you will need to do it without repeating any data. Filling or loading the Combo Box with a vector will allow our data to be viewed in a more comfortable way.
Arrays are a set of values of the same type and vectors are unidirectional arrays, before working with vectors it is important that you learn what dynamic vectors are and how they are used and under what condoms are used and how to resize them.
This can seem quite complicated for those who do not handle the correct methodology, and this spreadsheet has so many functions that it is difficult to know them all.
Configure the ComboBox
To begin, you must have the information you want to add to the ComboBox well ordered, a list within a table would be the best way.
Now, the next thing is to open or access the Visual Basic Editor, either through the ” Developer ” tab or with the ” Alt + F11 ” commands.
Next, inside the editor, look for the ” Insert ” button located on the top ribbon, and from the options displayed, you choose the one called ” UserForm “.
In this way, there will be a pop-up window, where the item “ Combo Box ” will be added from the lower menu of “ Controls ”.
Then, in the lower-left part of the screen, the name of the box can be changed to whatever the user wants. The next step is to double click on the form window.
Thus, the UserForm settings will open where, on the right side, there will be an arrow with the ” Events “, from which ” Initialize ” is chosen. This action will add the pertinent data to the window, where the pertinent code for what we are looking for will begin to be created.
The code
In this case, we will start by writing a series of variables that will provide and allow the form to work correctly. For that, it must be filled in as follows:
Private Sub UserForm_Initialize ()
Dim UniqueValue As Collection
Dim eng As Range
Dim cell As Range
Dim sh As Worksheet
Dim vNum As Variant
Set sh = ThisWorkbook.Worksheets (“Sheet1”)
Set Rng = sh.Range (“D4”, sh.Range (D4) .End (x1Down))
Set UniqueValue = New Collection
On Error Resume Next
For Each cell In Rng.Cells
UniqueValue.Add cell.Value, CStr (cell.Value)
Next cell
On Error GoTo 0
For Each vNum In UniqueValue
Me.UserForm.AddItem vNum
Next vNum
To finish, select the “ Play ” icon, the green arrow, “ Continue ” or “ F5 ” to execute the ComboBox, and in this way, it will take us directly to the spreadsheet.
How does this code work?
The importance of writing the code presented above is that there are certain elements in it that allow the data in the Excel ComboBox not to be repeated.
In this sense, the commands must be fully respected. In fact, one of the variables that proceed to omit repeated records is ” On Error Resume Next “.
In addition, the ” On Error GoTo 0 “, in the same way, influences how the form will proceed to take the data that is not unique. Now, something that must be taken into account is the name of the sheet that is being worked on.
Why? Well, because its title must be written as is in the line ” Set sh = ThisWorkbook.Worksheets (« Sheet1 ») ” of the code. Being ” Sheet1 ” just an example.
In addition to this, regarding the name that we have given to the ComboBox, it will also be essential to remember it and use it correctly.
Since, it must be written in the part of the code that says ” Me.UserForm.AddItem vNum “, replacing the ” UseForm ” with the title that has been chosen.
And in this way, an Excel ComboBox will have been added where the loaded values or data are not repeated.
Although, as has been seen, this procedure is usually put into practice by users who already have some handling of the functions that this calculation tool includes.
Therefore, if you want to continue working with this type of process, you may need to take some kind of Excel course or dedicate yourself to investigating all the possibilities based on trial and error.
Just as ComboBoxes work with vectors, they can also work with range data. Range data can also be passed without repeating.