First, I'm confused about where the comboboxes are located. I'm gonna guess
that they're all on a single sheet (Sheet1 is what I used).

Second, I wouldn't name those comboboxes CB1, CB2, ... That looks too much like
an address of a cell. Instead, I used CBX_01, CBX_02, CBX_04, ...

And these are comboboxes from the Control Toolbox toolbar, right?

This doesn't have much validation--no check to make sure that the number of
comboboxes matches the number of sheets, for instance.

Option Explicit
Public Sub SearchUnstamped()
Dim rng As Range
Dim Cel As Range
Dim NumberOfSheets As Long
Dim ShtCtr As Long
Dim CBXCtr As Long

CBXCtr = 0
NumberOfSheets = ThisWorkbook.Worksheets.Count

For ShtCtr = 5 To NumberOfSheets
With Sheets(ShtCtr)
Set rng = .Range("G6:G100")
CBXCtr = CBXCtr + 1
For Each Cel In rng.Cells
If Cel.Value = "" Then
If .Cells(Cel.Row, 1).Value < "" Then
If Cel.Interior.ColorIndex < 15 Then
Worksheets("Sheet1") _
.OLEObjects("CBX_" & Format(CBXCtr, "00")) _
.Object.AddItem .Cells(Cel.Row, 1).Value
End If
End If
End If
Next Cel
End With
Next ShtCtr
End Sub

Notice the dots in front of those .range() and .cells(). That means that these
belong to the object in the previous With statement--in this case "with
sheets(shtctr)"



James wrote:

Hi everyone. I need to add items to a combobox with the following criteria
p.s. the names of the comboboxes are cb1, cb2, cb3 etc. and the cb's are on
the worksheet, not in a userform.
also, do i need to set rng every time i activate a diff sheet? just wondering
Any Help would be great! Thanks

Public Sub SearchUnstamped()
Dim rng As Range
Dim n As Integer
Dim i As Integer
Dim j As Integer

Set rng = Range("G6:G100")
j = 1
n = ThisWorkbook.Worksheets.Count

For i = 5 To n
Sheets(i).Activate
j = j + 1
For Each cel In rng
If cel.Value = "" Then
If Cells(cel.Row, 1).Value < "" Then
If cel.Interior.ColorIndex < 15 Then
'Something like - cb(j).additem = Cells(cel.row,
1).value
'I need this section to take the value of column A
and add
'it to the combobox.
End If
End If
End If
Next
Next
end sub

--

Dave Peterson