Populating Combobox

[Rlb53]

I am trying to Populate a Combobox from a Named Range.
The Named Range is a Single Column
I have the following code.. but obviously I don't have it in proper order.
Could you assist please?

With Me.ComboBox1
.Value = ""
lr = Sheets("data sheet 2").Range(unit_measure, Rows.Count).End(xlUp).Row
.RowSource = Sheets("data sheet 2").Range(unit_measure & lr).Address(external:=True)

End With

Thanks !

[raji2678]

Instead of creating a combo box, you can do this:
1) Define a named range
2) Select the cell which you would like a dropdown. On the Data Validation tab, select List in the first dropdown, In the second dropdown, type
=AssignedTo
(Here AssignedTo is the name of my range)

Hope this helps

[Rlb53]

Thank you Raji,

The Combobox is on a Userform. I'm not quite certain how to apply your recommendation.

[raji2678]

Then specify your named range in the RowSource property. You can refer to this site:
http://support.microsoft.com/kb/161598

[Rlb53]

Thank you Again Raji,

I get an error message that says the information entered is an "Invalid Property Value"

I entered:
Sheets(data sheet 2)!Plant_No.
and
"Data Sheet 2"!Plant_No.
and some other combinations.

Sheet = "Data Sheet 2"
Range = "Plant_No."

The code below works Awesome. It populates the Combobox with only the values in the list and not a bunch of empty space once you reach the end of the list. But, further modification to the Workbook could cause a need to insert Columns on the Worksheet that would throw the code below out of whack. Hence the desire to use the Range Names.


Private Sub UserForm_Initialize()
With Me.Plant_No ' The Combobox Name
.Value = ""
' Column F = Range Name "Plant_No."
lr = Sheets("data sheet 2").Cells(Rows.Count, "f").End(xlUp).Row
' The Populating Information Starts at row 3 of the Named Range
.RowSource = Sheets("data sheet 2").Range("f3:f" & lr).Address(external:=True)
End With

End Sub

I also tried:

Me.Plant_No.List = Sheets("data sheet 2").Cells(Rows.Count, "Plant_No.").End(xlUp)

I may be entering the Information Incorrectly.

Thank you for your direction! But I'm still Lost.

[raji2678]

I am not sure if I can help, but I have attached a sample file.

[Rlb53]

I Tremendously Appreciate your Responses. I'm going to check out your file and see if it can set me straight ? I'll let you know.

[Rlb53]

Raji,

Thank you Again. I opened your example and it certainly will populate the combobox as demonstrated, but my entire column is designated as the Named Range. I want to limit the Combobox population to only the cells that have information in them. (which starts on the 3rd row of the Named Range)

As Data is entered into the workbook, the Combobox Population will Grow so I want to enable the .end(xlup) command somehow to keep from having all of the empty cells populate the combobox.

If we come up with the answer to this... it may very well assist Many !

Thank you for your Efforts. If you come up with any more suggestions.. I'm certainly open to respond.

[raji2678]

In that case, you can dynamically create the named range at runtime. I have worked on something similar, and trust me, this will work!

[Rlb53]

Hello All,

I'm still trying to overcome this task, but it is still kicking my butt (as simple as it probably is). I wanted to re-open the inquiry and see if I may once again ask for assistance with Direction?

Thank you,

[Kenneth Hobs]

There are several ways to do it.

'http://www.excelforum.com/excel-programming/793939-avoiding-repeating-values-when-building-a-combo-box-list.html
Private Sub UserForm_Initialize()
Dim r As Range, cell As Range
Set r = Range("B2:E2")
With CreateObject("Scripting.Dictionary")
.CompareMode = vbBinaryCompare
For Each cell In r
If cell.Value <> "" Then
If Not .Exists(cell.Value) Then .Add cell.Value, cell.Value
End If
Next cell
ComboBox1.List = .Keys()
End With
End Sub


'Excel Magic Trick 698, http://www.youtube.com/watch?v=IhuURsu0jdI
'http://contextures.com/xladvfilter01.html#FilterUR

Function UniqueComboBox(cbox As MSForms.ComboBox, uRange As Range) As Variant
Dim r As Range, cell As Range
Set r = uRange
With CreateObject("Scripting.Dictionary")
.CompareMode = vbBinaryCompare
For Each cell In r
If cell.Value <> "" Then
If Not .Exists(cell.Value) Then .Add cell.Value, cell.Value
End If
Next cell
cbox.List = .Keys()
End With
UniqueComboBox = WorksheetFunction.Transpose(cbox.List)
End Function