View original (French)

ComboBox: Display value from a range defined by name

Solved
xaveria -  
 xaveria -
Hello,

I have a form located in one sheet of my Excel workbook (Sheet1), and all the choice lists I want to have are in another sheet (Sheet3). All these lists have defined names in my workbook.

In my form, I want to have a dropdown list of choices, so I used the ComboBox, but I can't seem to display the desired values when I click the arrow of the list in the form. I'm not sure which event to choose to achieve this. The list remains empty.

Here is what is currently in the code:
Private Sub cbxSecteur_change()

Dim ChoixSecteurs As String
ChoixSecteurs = Sheet3.Range("Secteurs").Value

CbxSecteur.Rowsource = ChoixSecteurs

End Sub

Configuration: Windows 7 / Internet Explorer 8.0

1 answer

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    I have a form that is located in a sheet of my Excel workbook (Sheet1) Userform or controls on worksheet ???????
    0
    1. xaveria
       
      I have a command button on Sheet1 of the Excel Workbook, which opens a UserForm (FormulaireBox) in which I would like the ComboBoxes to have drop-down lists that are located in my Sheet3 of Excel (for example, the name of the range = Sectors).
      0
    2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Re,

      'VBA Userform 

      Private Sub UserForm_Activate()
      Dim pt As Integer, nb As Integer

      'clear list
      ComboBox1.Clear
      'number of values in the list
      nb = Worksheets("feuil3").Range("Secteurs").Cells.Count
      'loop to write value in combobox
      For pt = 1 To nb
      ComboBox1.AddItem (Worksheets("feuil3").Range("Secteurs").Cells(pt, 1))
      Next pt
      End Sub

      'or 

      Private Sub UserForm_Activate()
      ComboBox1.Clear
      'filling by address of the range
      ComboBox1.RowSource = Worksheets("feuil3").Range("A1:A5").Address
      End Sub

      'or

      'Directly in the RowSource property of ComboBox1: Sectors

      A+
      0
    3. xaveria
       
      It doesn't work, I adjusted the name of the combobox to the name of my combobox, and A1:A5 to the name of my range, and it doesn't work, I have an error message that appears on the screen, the index is not part of the selection: It doesn't seem to find my range in my Excel sheet named "Secteurs"

      Private Sub UserForm_Activate()
      CbxSecteur.Clear
      'filling by address of the range
      CbxSecteur.RowSource = Worksheets("feuil3").Range("Secteurs").Address

      End Sub
      0
    4. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Re,

      CbxSecteur.RowSource = Worksheets("feuil3").Range("Secteurs").Address

      It doesn't work, that's why I hadn't included it, but by using an intermediate variable it works, funny how that is!!! 

      Private Sub UserForm_Click()
       x = Worksheets("feuil1").Range("liste").Address
       ComboBox1.RowSource = x
      End Sub

      Catch you later!
      0
    5. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Hello,

      I forgot the name of the sheet:
      ex:
      Private Sub UserForm_Activate()
      x = "sheet3!" & Worksheets("sheet3").Range("list2").Address
      ComboBox1.RowSource = x
      End Sub
      0