This may seem complicated enough for those who don't handle it correct methodology and this spreadsheet has so many functions that it's hard to know them all.
Configure the ComboBox
To get started, you need to have the information you want to add to the ComboBox well sorted, a list within a table would be the best way.
Now, the next thing is to open or access the Visual Basic editor, via the » Developer »Or with the commands» Alt + F11 «.
Then, in the editor, look for the button " Inserisci »Located on the top ribbon and, from the options displayed, choose the one called» User form «.
This will open a pop-up window, where the item " Combo box "From the lower menu of" Controls ".
Then, in the lower left part of the screen, the box name can be changed to whatever the user wants. The next step is to double-click the module window.
This will open the UserForm settings where, on the right side, there will be an arrow with the " Events ", From which you choose" Initialize ". This action will add the relevant data to the window, where it will start creating the relevant code for what we are looking for.
Code
In this case, we will start by writing a series of variables that will provide and enable the form to work properly. To this end, it must be completed as follows:
Private Secondary User Form_Initialize ()
Dim UniqueValue As Collection
Dim ita As Range
Weak cell as a range
Dim sh as a worksheet
Dim vNum come variant
Imposta sh = ThisWorkbook.Worksheets («Foglio1»)
Imposta Rng = sh.Range («D4», sh.Range (D4).End (x1Down))
Set UniqueValue = New Collection
In case of an error Resume Next
For each cell in Rng.Cells
UniqueValue.Add cell.Value, CStr(cell.Value)
Next cell
On Error Go to 0
By posing vNum in UniqueValue
Me.UserForm.AddItem vNum
Next v number
Finally, select the icon " Play ", The green arrow," Continue " or " F5 ”To run 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 some elements in it that allow you to not repeat the data in the Excel ComboBox.
In this sense, the commands must be fully respected. In fact, one of the variables that skips repeated records is " On Error Resume Next ".
Furthermore, the " On Error GoTo 0 «, In the same way it affects how the form will proceed to take data that is not unique. Now, something that needs to be taken into consideration is the name of the sheet you are working on.
How come? Well, because the title has to be written as it is in the line " Set sh = ThisWorkbook.Worksheets (» Sheet1 «) "Of the code. Being " Sheet1 Simply an example.
In addition to this, as for the name 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" 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 do not repeat.
Although, as we have seen, this procedure is usually put into practice by users who already have some management of the functions that includes this calculation tool.
Therefore, if you want to continue working with this type of process, you may need to take some sort of Excel course or devote yourself to studying all possibilities based on trial and error.
Just as ComboBoxes work with vectors, they can also work with range data. The interval data can also be passed without repeating.
