excelvba

How populate a combobox from other sheet VBA Excel Run-time error '438


I try to pupolate a combobox in a sheet with information of other sheet

enter image description here

I put code in module but return the error: Run-time error '438': Object doesn't support this property or method

Sub Add_ComboBox_From_Another_Worksheet()
Set contacts = ThisWorkbook.Worksheets("Sheet_Contacts")
Set comboContacts = ThisWorkbook.Worksheets("Sheet_Invoice_Template")
contacts.ComboBoxClientes = contacts.Range("A2:A" & contacts.Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Solution

  • Sub Add_ComboBox_From_Another_Worksheet()
    
        Dim wsContacts As Worksheet, wsInvoice As Worksheet
        Dim cmb As Object, lastrow As Long
        
        With ThisWorkbook
            Set wsContacts = .Worksheets("Sheet_Contacts")
            Set wsInvoice = .Worksheets("Sheet_Invoice_Template")
        End With
        
        Set cmb = wsInvoice.OLEObjects("ComboBoxClientes")
        
        With wsContacts
            lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
            cmb.ListFillRange = .Range("A2:A" & lastrow).Address(0, 0, , True)
        End With
        
    End Sub