Excel VBA : Adding Drop-Down List to ComboBox

[RAKESH230583]

Hi,

I have one Excel Userform, Under which I have 7 Different comboBox and for source for the every single combobox I had used the below quoted VBA under Private Sub UserForm_Activate()

Under Sheet2("Source") - under column range A:G - I had listed all the value which I required to show under each combobox.

Below quoted VBA code works fine for me - But Is there any possibility that instead of using such big and complicated code I can use the simple and short code which will give me the same results ?

Private Sub UserForm_Activate()

Dim Ws As Worksheet
Dim i As Integer
Set Ws = ThisWorkbook.Sheets("Source")

Me.ComboBox1.Clear
For i = 2 To Ws.Range("A" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox1.AddItem Ws.Range("A" & i).Value
Next I

Me.ComboBox2.Clear
For i = 2 To Ws.Range("B" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox2.AddItem Ws.Range("B" & i).Value
Next I

Me.ComboBox3.Clear
For i = 2 To Ws.Range("C" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox3.AddItem Ws.Range("C" & i).Value
Next I

Me.ComboBox4.Clear
For i = 2 To Ws.Range("D" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox4.AddItem Ws.Range("D" & i).Value
Next I

Me.ComboBox5.Clear
For i = 2 To Ws.Range("E" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox5.AddItem Ws.Range("E" & i).Value
Next I

Me.ComboBox6.Clear
For i = 2 To Ws.Range("F" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox6.AddItem Ws.Range("F" & i).Value
Next I

Me.ComboBox7.Clear
For i = 2 To Ws.Range("G" & Application.Rows.Count).End(xlUp).Row
Me.ComboBox7.AddItem Ws.Range("G" & i).Value
Next I

Me.Date_Picker.Value = Date

End Sub

 

[RoryA]

Since your combo names and column numbers correspond, you can use a simple loop:

Private Sub UserForm_Activate()

Dim Ws As Worksheet
Dim i As Long
Dim n as long
Set Ws = ThisWorkbook.Sheets("Source")

For n = 1 to 7
Me.Controls("ComboBox" & n).List = Ws.Range(ws.Cells(2, n), ws.cells(ws.Rows.Count, n).End(xlUp)).Value
Next

Me.Date_Picker.Value = Date

End Sub

 

[RAKESH230583]

Thanks - Its works the same - But now with short and simple code its looks more logical.

 

[RAKESH230583]

I have used the above suggested vba code - and able to get the correct combobox list too.

Can anyone please tell me how can i validate the said list, as i dont want user to manual type the data which in not under the source list.

And if any user try to manually input details which is not under the source list - Then they should get the error message "Incorrect selection - Kindly select the data from the list available or request to update the source list"

 

[RAKESH230583]

Hi - can anyone please assist me on above request.

 

[RoryA]

Just change the combobox's Style property to 2 and the users will not be able to type anything that isn't in the list.