Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
Watch for line wrap in that previous post. I missed fixing a long line and it
wrapped! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add item to combobox in sheet
ok, ill give that a try. thanks again!
"Dave Peterson" wrote: Watch for line wrap in that previous post. I missed fixing a long line and it wrapped! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display the first Item in a combobox as the default item | Excel Programming | |||
How to display the first Item in a combobox as the default item | Excel Programming | |||
item limit for a combobox? | Excel Worksheet Functions | |||
Don't add item in combobox ... | Excel Programming | |||
Combobox Add Item | Excel Programming |