Add item to combobox in sheet

[James]

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]

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]

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]

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]

no, what i need to do is refresh the list (or re populate it) so i was going
to clear the contents of each dropdown and then repopulate with the code you
gave me. The value in column G wont always be = "".

just like Userform1.Combobox1.Clearcontents but i dont want to type this 55
times (thats how many combos i have) this is why i wanted to do a for loop.
Thanks for the help. I really appreciate it.

 

[Dave Peterson]

Maybe...

Dim CBXCtr As Long

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

I'd do this in the same code that populated the comboboxes:

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

NumberOfSheets = ThisWorkbook.Worksheets.Count

For ShtCtr = 5 To NumberOfSheets
Worksheets("Sheet1").OLEObjects("CBX_" & Format(CBXCtr - 4, "00")) _
.Object.Clear
With Sheets(ShtCtr)
Set rng = .Range("G6:G100")
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(ShtCtr - 4, "00"))
_
.Object.AddItem .Cells(Cel.Row, 1).Value
End If
End If
End If
Next Cel
End With
Next ShtCtr
End Sub

Notice that I dropped the CBXCtr stuff. I just used ShtCtr-4. With your naming
convention, that seems equivalent to me.

no, what i need to do is refresh the list (or re populate it) so i was going
to clear the contents of each dropdown and then repopulate with the code you
gave me. The value in column G wont always be = "".

just like Userform1.Combobox1.Clearcontents but i dont want to type this 55
times (thats how many combos i have) this is why i wanted to do a for loop.
Thanks for the help. I really appreciate it.

 

[Dave Peterson]

Watch for line wrap in that previous post. I missed fixing a long line and it
wrapped!

 

[James]

ok, ill give that a try. thanks again!