Thread: Loop Worksheet Combo Boxes

Hi all,

I am working with some worksheet combo boxes and I am having troubles with loading values into them using a loop. The code:

For cbCnt = 1 To 2
        If cbCnt = 1 Then
            For Each rCell In qtrSht.Range(qtrSht.Cells(3, 3), qtrSht.Cells(3, 3).End(xlDown))
                With OLEObjects("cbDt" & cbCnt)
                    .AddItem rCell.Value
                End With
                        
            Next rCell
        End If
Next cbCnt

Can anyone tell me why this is be so darned stubborn?

Supashi-bo

Hi Mordred,

With OLEObjects("cbDt" & cbCnt).Object

Unless your code is in the worksheet's class module, you'll also need to qualify the OLEObjects property, eg.

With qtrSht.OLEObjects

Assuming they are ActiveX comboboxes.

They are ActiveX comboboxes. Thanks guys, I will try this right now!

That fixed it Colin, thank you!

With OLEObjects("cbDt" & cbCnt).Object

was the answer!
Much appreciated!

Michael

You don't need any loop.
You'd better avoid additem to populate comboboxes/listboxes.

with qtrSht
  .cbDt1.list=.cells(3,3).resize(.Cells(rows.count, 3).End(xlUp).row-2).value
  .cbDt2.list=.cells(3,5).resize(.Cells(rows.count, 5).End(xlUp).row-2).value
end with

Hi snb, why avoid .AddItem? Here is the code for a combobox clear and then refill that I am troubled by:

Sub btClear_Click()

    Dim frmSht As Worksheet, qtrSht As Worksheet
    Dim lbCnt As Long, lbCntr As Long, cbCnt As Long, cbCntr As Long
    Dim rCell As Range
    Set qtrSht = Worksheets("Qrtr")
    Set frmSht = Worksheets("Sheet2")
    For cbCnt = 1 To 2
        If cbCnt = 1 Then
            frmSht.OLEObjects("cbDt" & cbCnt).Object.Clear
            For Each rCell In qtrSht.Range(qtrSht.Cells(3, 3), qtrSht.Cells(3, 3).End(xlDown))
                If cbCnt = 1 Then
                With frmSht.OLEObjects("cbDt" & cbCnt).Object
                        .Top = 99.75
                        .Width = 119.25
                        .Height = 19.5
                        .AddItem rCell.Value
                End With
                End If
            Next rCell
        End If
    Next cbCnt
    For some reason I cannot set the dimensions and the top.
End Sub

Additem isn't designed for populating listboxes/comboboxes; .list is. And additem is terribly slooooooow...
If you use .List you won't need to use .clear, because .list replaces the Listbox's whole content.
This suffices:

Sub btClear_Click()
  with sheets("Sheet2")
    .cbDt1.list=sheets("Qtr").columns(3).specialcells(2).offset(2).specialcells(2).value
    .cbDt2.list=.cbDt1.list
  end with
End Sub

Originally Posted by snb

Additem isn't designed for populating listboxes/comboboxes

Really? On what do you base that statement?