Ignoring blank cells in a combo box...

[Rutgers_Excels]

Is there any way I can ignore blank cells when using a combo box fro
the forms menu? For instance, my combo box is being filled by th
range A1:A5. However, if A3 is blank, I don't want it ( a blank) t
show up in the drop down box. Is there is a more efficient way to d
this or it might it be more simple to use a combo box from the contro
toolbox?

Any help would be appreciated.

Thanks

 

[Dave Peterson]

The combobox from the Forms toolbar is also called a DropDown.

And you could use a little code that filled up that dropdown:

I chose to put it into the worksheet_activate event. But you could put it
whereever you need:

Option Explicit
Private Sub Worksheet_Activate()
Dim myRng As Range
Dim myCell As Range
Dim myDD As DropDown

Set myRng = Me.Range("a1:a5")
Set myDD = Me.DropDowns("drop down 1")

myDD.RemoveAllItems

For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
myDD.AddItem myCell.Value
End If
Next myCell

End Sub


Debra Dalgleish has some neat ways to use Data|Validation. She has one that
hides previously selected items. Maybe you could change it to hide the blank
values.

http://www.contextures.com/xlDataVal03.html