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.
James wrote:
>
> 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" wrote:
>
> > 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
> >
--
Dave Peterson
|
Similar Threads
