CODEWITHSUNDEEP
excelvbacomboboxuserform
Gonçalo Geraldes
Gonçalo Geraldes

Reputation: 15

Fill two ComboBoxes with table values

I have a UserForm that has two ComboBoxes.

Private Sub UserForm_Initialize()

''''''' Preencher com os bancos ''''''

    Dim i As Long
    Dim n As Long
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Sheets("Spreads")

    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

    For i = 2 To n
        Me.BancoBox.AddItem Cells(i, 1)
    Next i
    
''''''' Preencher com os fornecedores ''''''
    
    Dim y As Long
    Dim f As Long
    Dim forn As Worksheet
    
    Set forn = ThisWorkbook.Sheets("Fornecedores")
    
    f = forn.Range("A" & Application.Rows.Count).End(xlUp).Row
    
    For y = 2 To f
        Me.FornecedorBox.AddItem Cells(y, 1)
    Next y
    
End Sub

One ComboBox is for names of banks and the other is for names of suppliers which are in the "Spreads" and "Fornecedores" sheets accordingly.

They both return the name of the banks.

Upvotes: 0

Views: 55

Answers (2)

SJR
SJR

Reputation: 23081

When adding the items you omitted a sheet reference so the active sheet was assumed. Since this does not change in your code the same sheet was referenced each time.

Private Sub UserForm_Initialize()

Dim i As Long
Dim n As Long
Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Spreads")
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

For i = 2 To n
    Me.BancoBox.AddItem sh.Cells(i, 1) 'sheet reference
Next i

Dim y As Long
Dim f As Long
Dim forn As Worksheet

Set forn = ThisWorkbook.Sheets("Fornecedores")
f = forn.Range("A" & Application.Rows.Count).End(xlUp).Row

For y = 2 To f
    Me.FornecedorBox.AddItem forn.Cells(y, 1) 'sheet reference
Next y
    
End Sub

Note though that the quickest way to populate a combobox is to use List property and then you can avoid loops altogether, e.g.

Me.FornecedorBox.List = forn.Range("A2:A" & f).Value

Upvotes: 1

cristelaru vitian
cristelaru vitian

Reputation: 141

You should activate the sheets before performing any action on them.

Upvotes: 0

Related Questions