Combobox method help 1

[osx99]

I have a combobox in excel which populates from a dynamic range so 'Listfillrange = worktype'

However the list has blank rows so an example combobox list will loook something like

Repair
BLANK
BLANK
BLANK
Strengthen
BLANK
BLANK
etc


I want to remove the BLANK itmes in the list and have tried the following code unsuccessfully

Private Sub ComboBox2_Click()
For i = 0 To ComboBox2.ListCount
   If LenB(ComboBox2.ListIndex(i)) = 0 Then
      ComboBox2.RemoveItem (i)
   End If
Next i
End Sub

Can anyone help witht the syntax for this code which fails on If LenB(ComboBox2.ListIndex(i)) = 0?

Thanks
Os

 

[genomon]

How about not putting it in the combo box in the first place when you do .AddItem?

Illegitimi non carborundum.

 

[fumei]

1. I am pretty sure Skip would rant about having blank rows as a design flaw.

2. This may help.

Private Sub cmdRemoveBlanks_Click()
Dim i As Long
Dim j As Long
Dim KeepThese()

For i = 0 To ComboBox1.ListCount - 1
[COLOR=red]   '  build the list of what to KEEP[/color red]
   If ComboBox1.List(i, 0) <> "" Then
      ReDim Preserve KeepThese(j)
      KeepThese(j) = ComboBox1.List(i, 0)
      j = j + 1
   End If
Next

[COLOR=red]' clear EVERYTHING[/color red]
ComboBox1.Clear
[COLOR=red]' add back in the ones not "BLANK"[/color red]
For i = 0 To UBound(KeepThese())
   ComboBox1.AddItem KeepThese(i)
Next i
End Sub

Private Sub UserForm_Initialize()
   ComboBox1.AddItem "Repair"
   ComboBox1.AddItem ""
   ComboBox1.AddItem ""
   ComboBox1.AddItem ""
   ComboBox1.AddItem "Strengthen"
   ComboBox1.AddItem ""
   ComboBox1.AddItem ""

End Sub

The rows may be "blank", but AddItem will put "" into the item. So, loop through checking to see what is "", and what is not "". Build a list of what to keep, clear everything, and then put back in what you want to keep.

The problem with your looping was that as you executed RemoveItem, the ListCount changed. Further,

LenB(ComboBox2.ListIndex(i)) = 0

does not really work as ListIndex is a number (regardless of whether the item text is "Repair", or ""). So LenB will never = 0.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[osx99]

fumei,

Thanks for the help.

I've adapted your code a little to give

Private Sub ComboBox1_gotfocus()

Dim i As Long

Me.ComboBox1.List = Worksheets("70FiNAL").Range("g1:h1000").Value

For i = ComboBox1.ListCount - 1 To 0 Step -1

   If ComboBox1.List(i, 0) = "" Then
   With ComboBox1
   .RemoveItem (i)
   
   End With
   End If
Next
   

'Next i
End Sub

 

[fumei]

cute

As it stands, you do not need the With.

Private Sub ComboBox1_gotfocus()
Dim i As Long

Me.ComboBox1.List = _
       Worksheets("70FiNAL").Range("g1:h1000").Value

For i = ComboBox1.ListCount - 1 To 0 Step -1
   If ComboBox1.List(i, 0) = "" Then
      ComboBox1.RemoveItem (i)
   End If
Next
End Sub

Although......that sure seems like it could be one heck of a huge dropdown list. Are you sure about this?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[osx99]

Although......that sure seems like it could be one heck of a huge dropdown list. Are you sure about this?"

I'm not sure yet?

Is there a neater way to implement the attached? Each combobox on row 25 loads the full list during the event gotfocus. Then based on previous combobox selection removes items from the list not required.

I thought speed wasn't too bad but any suggestions on a better solution are greatly appreciated

The user wants ability to change and update source data in columns CUE easily

Thanks
Os