You want to keep the values in the dropdowns, but make the comboboxes appear
empty?

Option Explicit
Public Sub ClearComboboxes()
Dim CBXCtr As Long

For CBXCtr = 5 To Worksheets.Count
Worksheets("Sheet1").OLEObjects("CBX_" & Format(CBXCtr - 4, "00")) _
.Object.ListIndex = -1
Next CBXCtr
End Sub

James wrote:

thanks so much dave, that works great. One other question. how do i clear the
contents of each box?
I want to clear contents after a button is pressed. something like this
would be great
for each ctrl in comboboxes
clearcontents
next

"Dave Peterson" wrote:

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


--

Dave Peterson