VBA Excel Combobox issue

Question

I am trying to make an application which lets you update customer supplier details using a combobox.

See this screenshot for a demo.

On selecting a supplier (Fournisseur in French) in the combobox I want to be able to:

1. Display the supplier details in my s/sheet in the fields on the combobox
2. Change a field (e.g. address) on the combobox and write it back to the s/sheet

Here is my code to initialize the user from:

Private Sub UserForm_Initialize()

   'initialisation des variables
    Ligne = 2 'Affectation du numéro de colonne à 2 car premiere donnée
    'On réalise une boucle qui va charger les noms des régions dans la liste déroulante
    Sheets("Fournisseurs").Select
    Do While Cells(Ligne, 1).Value <> "" 'Tant qu'il y a des valeurs dans la colonne 1, on charge les noms du fournisseur

        FormModif_Fournisseur.cboNom.AddItem Cells(Ligne, 1).Value
        Ligne = Ligne + 1 'On incrémente le numéro de ligne afin de passer à la ligne suivante

    Loop

 End Sub

I think to do what I am asking I may need to use code like this:

Private Sub cboNom_Change()
     // Do some stuff!
End Sub

Thanks

Answer 1

Let assume your data is in A2:A4 as follows:

1   Nom          Adresse            Telephone    Fax
2   Carrefour    1, Gare du Nord    111 222 333  01 02 03
3   Gandi        1, rue de la croix 444 555 666  04 05 06
4   Hermes       1, Champs Elysees  777 888 999  07 08 09

I also have a userform (FormModif_Fournisseur) that looks like this:

I launch the form using an ActivX button on the s/sheet:

Private Sub CommandButton1_Click()
    FormModif_Fournisseur.Show
End Sub

The following code is now in the UserForm. Here is my labeling pattern:

• cbNom = combobox for suppliers
• tbAddress = address field
• tbTel = telephone field
• tbFax = fax field
• btnSave = save button
• btnCancel = cancel button

~~~> Populates the combobox on launch

Private Sub UserForm_Initialize() 
    Dim suppliers As Range

    Set suppliers = Worksheets("Sheet1").Range("A2:A" & Worksheets("Sheet1").Range("A2").End(xlDown).Row)

    Me.cbNom.MaxLength = suppliers.Count
    Me.cbNom.List = suppliers.Value
End Sub

~~~> Pulls in address, tel, and fax based on supplier name

Private Sub cbNom_Change()
    Dim data As Range

    Set data = Worksheets("Sheet1").Range("A2:D" & Worksheets("Sheet1").Range("A2").End(xlDown).Row)

    Me.tbAddress = WorksheetFunction.VLookup(Me.cbNom.Value, data, 2, False)
    Me.tbTel = WorksheetFunction.VLookup(Me.cbNom.Value, data, 3, False)
    Me.tbFax = WorksheetFunction.VLookup(Me.cbNom.Value, data, 3, False)
End Sub

~~~> Saves changes to s/sheet for address, tel, and fax based on supplier name

Private Sub btnSave_Click()
    Dim suppliers As Range, rowMatch As Integer

    //Set reference to suppliers 
    Set suppliers = Worksheets("Sheet2").Range("A2:A" & Worksheets("Sheet1").Range("A2").End(xlDown).Row)

    //Get the row of the supplier in the s/sheet
    rowMatch = WorksheetFunction.Match(Me.cbNom.Value, suppliers, 0) + 1

    //Write data to appropriate cell on s/sheet
    With Worksheets("Sheet2")
        .Range("B" & rowMatch) = Me.tbAddress
        .Range("C" & rowMatch) = Me.tbTel
        .Range("D" & rowMatch) = Me.tbFax
    End With
End Sub

~~~> Cancels userform

Private Sub btnCancel_Click()
    Me.Hide
End Sub

Answer 2

The following code tries

a) to increase performance by filling the Combobox via Array method thus avoiding a Loop via AddItem (ListBoxes and ComboBoxes are pure Arrays). Furthermore this avoids the limitation of adding no more than 10 columns to the control. The solution avoids the unnecessary and slower WorksheetFunction Match, as well.

b) to simplify the process using a doFillCboNom Sub procedure called as well by the UserForm_Initialize proc as by the btnActualize_Click Event.

Bonne Chance!

Option Explicit
Dim rngData As Range


Private Sub UserForm_Initialize()
' call sub filling ComboBox cboNom
  doFillCboNom
End Sub

Private Sub UserForm_Layout()
' Format ComboBox cboNom
  Me.cboNom.ColumnCount = 2                 ' two columns Id + Name    
  Me.cboNom.ColumnWidths = "30;80"          ' ColumnWidths in Points
End Sub

Private Sub btnSave_Click()
  Dim ligne As Long
' no action if no data in comboBox
  If Me.cboNom.ListCount < 1 Then Exit Sub
' a) unsorted combo
' get line nr changing the zero based ListIndex of ComboBox cbNom 
' to one base
  ligne = Me.cboNom.ListIndex + 1
' b) if you use a sorted Combo, replace above code with the following line:
'    ligne = getLigne()


' Save data to sheet
  rngData(ligne, 2).Offset(0, 1).Value = Me.tbAddress.Text
  rngData(ligne, 2).Offset(0, 2).Value = Me.tbTel.Text
  rngData(ligne, 2).Offset(0, 3).Value = Me.tbFax.Text

End Sub

Private Sub btnActualize_Click()
    doFillCboNom
End Sub

Private Sub cboNom_Change()
  Dim ligne As Long
' no action if no data in comboBox
  If Me.cboNom.ListCount < 1 Then Exit Sub
' a) unsorted combo
'    get line nr changing the zero based ListIndex of ComboBox cbNom 
'    to one base
  ligne = Me.cboNom.ListIndex + 1
' b) if you use a sorted Combo, replace above code with the following line:
'    ligne = getLigne()

' Fill data
' with line nr refer to the datafield line column B and
' get the wanted sheet values 1,2,3 cells to the right
  Me.tbAddress.Text = rngData(ligne, 2).Offset(0, 1).Value  ' Column C
  Me.tbTel.Text = rngData(ligne, 2).Offset(0, 2).Value      ' Column D
  Me.tbFax.Text = rngData(ligne, 2).Offset(0, 3).Value      ' Column E
End Sub


Private Sub doFillCboNom()
' Purpose: fill ComboBox cboNom
' Note:    called by UserForm_Initialize and btnActualize_Click
' Declarations
  Dim liMax   As Long
  Dim ws      As Worksheet
' address WorkSheet
  Set ws = ThisWorkbook.Sheets("Fournisseurs")
' get last line of Range
  liMax = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
' alternative method:
'    liMax = ws.Range("A2:A" & ws.Range("A2").End(xlDown).Row).Count
' set data field
  Set rngData = ws.Range("A2:E" & Format(liMax, 0))
' Fill ComboBox via datafield at once
  Me.cboNom.List = rngData.Value
' Show first supplier (fournisseur) - ListIndex 0 as combobox is zero based
  Me.cboNom.ListIndex = 0
End Sub

Remark

If you use a sorted Combo box then you'll have to replace the code

ligne = ligne = Me.cboNom.ListIndex + 1

by

  ligne = getLigne()

calling a user defined function (instead of WorkSheetFunction Match)

Private Function getLigne() As Long
Dim i As Long, imax As Long                     ' Line nr
Dim s As String                                 ' ID supplier/fournisseur
    s = Me.cboNom.List(Me.cboNom.ListIndex, 0)
imax = rngData.Rows.Count
For i = 1 To imax
   If rngData(i, 1) = s Then
      getLigne = i
      Exit Function
   End If
Next i
End Function