View Full Version : Leave out blank items in combobox
enfantter
09-08-2007, 06:20 AM
Hi!!
Does anybody know if there is a procedure that will help me leave out blank items in a combobox.
The items in the combobox comes from a range in another sheet where there are some blanks.
thnx alot!
/Jack
lucas
09-08-2007, 07:25 AM
combobox from the forms toolbar on the sheet?
combobox from the control toolbox on the sheet?
combobox on a userform?
data validation drop down on a sheet?
enfantter
09-08-2007, 07:38 AM
i get blank cells in a combobox in a userform.
those are the ones that i would like to get rid of.
lucas
09-08-2007, 07:50 AM
Hi Jack,
This will return a list of unique items from the column with no blanks.
Option Explicit
Private Sub UserForm_Initialize()
Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long
Set Rng1 = Sheets("Sheet1").Range("A3:A1103")
y = 1
ReDim UniqueList(1 To Rng1.Rows.Count)
For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.ComboBox1.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next
End Sub
enfantter
09-08-2007, 08:05 AM
Perfect - works fine.
Just what I needed!!
lucas
09-08-2007, 08:11 AM
And if you don't want a unique list of items from the column but you want no blank spaces just change it like this:
Option Explicit
Private Sub UserForm_Initialize()
Dim Rng1 As Range
Dim c As Range
Set Rng1 = Sheets("Sheet1").Range("A3:A1103")
For Each c In Rng1
If Not c.Value = vbNullString Then
Me.ComboBox1.AddItem (c.Text)
End If
Next
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.