CODEWITHSUNDEEP
vbaexcel
Wilson Méndez Peñalosa
Wilson Méndez Peñalosa

Reputation: 41

Refresh combo box after list update vba excel

I'm trying to auto update a combobox list. It updates correctly only when I close and then open the workbook, or when I press the stop button on VBA and run the macro again. I have the following VBA code. 

Private Sub UserForm_Initialize()
Dim cod As Range
Dim pro As Range
Dim cli As Range
Dim ws As Worksheet
Dim ws5 As Worksheet
Set ws = Worksheets("ListaProductos")
Set ws5 = Worksheets("ListaClientes")

For Each cod In ws.Range("CodigoProductoLista")
    With Me.codigo
        .AddItem cod.Value
        .List(.ListCount - 1, 1) = cod.Offset(0, 1).Value
    End With
Next cod

For Each cli In ws5.Range("ClienteLista")
    With Me.cliente
        .AddItem cli.Value
        .List(.ListCount - 1, 1) = cli.Offset(0, 1).Value
    End With
Next cli

No.Value = True
calendario2.Visible = False
calendario2.Refresh
calendario = Date
Me.codigo.SetFocus
End Sub

Thanks!

Upvotes: 0

Views: 9409

Answers (2)

NeepNeepNeep
NeepNeepNeep

Reputation: 913

That Initialize event will only trigger when the form is loading. Add a button to your form called cmdRepopulate and use this code instead:

Option Explicit

Private Sub UserForm_Initialize()
    PopulateCodigoProductoLista
    PopulateClienteLista
    FinishingOff
End Sub

Private Sub PopulateCodigoProductoLista()
    Dim rngData As Range

    With Worksheets("ListaProductos").Range("CodigoProductoLista")
        Set rngData = .Resize(.Rows.Count, 2)
    End With

    PopulateComboUsingRange Me.codigo, rngData

End Sub

Private Sub PopulateClienteLista()
    Dim rngData As Range

    With Worksheets("ListaClientes").Range("ClienteLista")
        Set rngData = .Resize(.Rows.Count, 2)
    End With

    PopulateComboUsingRange Me.cliente, rngData
End Sub

Private Sub FinishingOff()
    No.Value = True
    calendario2.Visible = False
    calendario2.Refresh
    calendario = Date
    Me.codigo.SetFocus
End Sub

Private Sub PopulateComboUsingRange(cboDataDestination As MSForms.ComboBox, _
                                    rngDataSource As Range)

    Dim lngCounter  As Long

    With cboDataDestination
        .Clear
        For lngCounter = 1 To rngDataSource.Rows.Count

            .AddItem rngDataSource.Cells(lngCounter, 1)

            If rngDataSource.Columns.Count = 2 Then
                .List(.ListCount - 1, 1) = rngDataSource.Cells(lngCounter, 2)
            End If
        Next
    End With
End Sub

Private Sub cmdRepopulate_Click()
    PopulateCodigoProductoLista
    PopulateClienteLista
    FinishingOff
End Sub

There are some limitations (because it's too late), notably the generic combo population routine but in its current for it should be good for you. The code will run on the form's Initialize event and whenever you click the repopulate button. I decided to work with your named ranges as they were - no changes.

Note the use of a generic proc to populate the combo. If you had other combos on other forms in this same workbook, you could move that proc to a separate module, change it to Public (rather than Private) and save yourself a lot of typing by reusing the code.

Upvotes: 0

Bas Verlaat
Bas Verlaat

Reputation: 852

You could call the UserForm_Initialize procedure again, but you will have to clear the lists first. You could use it in a commandbutton, or in an event for instance.

Upvotes: 1

Related Questions