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

Answer 1 / 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
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
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
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
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
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