Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Hi !
I have my Form with one Combobox. I need it to show column 1 and 2. And it is ok. The code is: i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then Me.ComboBox1.RowSource = "Pedidos!A" & i & ":B" & i Me.ComboBox1.AddItem 'HERE IS THE PROBLEM With ComboBox1 .ColumnWidths = "50;80" End With End If Wend How could I add the item every time one line is in accordance with my condiction? Using the code above it just gives me the last row... =( Someone help me? Eddie. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
You cannot use RowSource and then AddItem. The AddItem can only be used
when the original method of populating the combobox was by AddItem or Array. See VBA help file under AddItem for details. "Eddie_SP" wrote in message ... Hi ! I have my Form with one Combobox. I need it to show column 1 and 2. And it is ok. The code is: i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then Me.ComboBox1.RowSource = "Pedidos!A" & i & ":B" & i Me.ComboBox1.AddItem 'HERE IS THE PROBLEM With ComboBox1 .ColumnWidths = "50;80" End With End If Wend How could I add the item every time one line is in accordance with my condiction? Using the code above it just gives me the last row... =( Someone help me? Eddie. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
One option would be to make your RowSource range dynamic so that it will
adjust if an item is added in that range. That would then require that after an item is added that the range be re-initialized so that it picks up the new item. Otherwise, you will have to change the method of loading the combobox so that is uses the AddItem method when the form is initialized. "Eddie_SP" wrote in message ... Hi ! I have my Form with one Combobox. I need it to show column 1 and 2. And it is ok. The code is: i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then Me.ComboBox1.RowSource = "Pedidos!A" & i & ":B" & i Me.ComboBox1.AddItem 'HERE IS THE PROBLEM With ComboBox1 .ColumnWidths = "50;80" End With End If Wend How could I add the item every time one line is in accordance with my condiction? Using the code above it just gives me the last row... =( Someone help me? Eddie. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
I tried this:
Private Sub UserForm_Initialize() Dim i As Integer Dim ComboRange As Range i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then ComboRange = Range("A" & i & ":B" & i) With ComboBox1 .ColumnWidths = "50;80" .AddItem ComboRange End With End If Wend End Sub But it doesn't work... =( |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Private Sub UserForm_Initialize()
Dim i As Long Dim ComboRange As Range With me.ComboBox1 'just do this stuff once--not in a loop .columncount = 2 .ColumnWidths = "50;80" i = 5 While (ActiveSheet.Cells(1 + i, 11).value < 0) i = i + 1 If ucase(Cells(1 + i, 11).Text) < "OK" _ And Cells(1 + i, 11).Text < "" Then .additem cells(i,"A").value .list(.listindex-1) = cells(i,"B") End If Wend end with End Sub (Untested, uncompiled. Watch for typos.) Eddie_SP wrote: I tried this: Private Sub UserForm_Initialize() Dim i As Integer Dim ComboRange As Range i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then ComboRange = Range("A" & i & ":B" & i) With ComboBox1 .ColumnWidths = "50;80" .AddItem ComboRange End With End If Wend End Sub But it doesn't work... =( -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Typo!!!!
Change this line: ..list(.listindex-1) = cells(i,"B") to ..list(.listindex-1, 1) = cells(i,"B").Text I missed the second dimension and I didn't qualify the range (.value or .text). Dave Peterson wrote: Private Sub UserForm_Initialize() Dim i As Long Dim ComboRange As Range With me.ComboBox1 'just do this stuff once--not in a loop .columncount = 2 .ColumnWidths = "50;80" i = 5 While (ActiveSheet.Cells(1 + i, 11).value < 0) i = i + 1 If ucase(Cells(1 + i, 11).Text) < "OK" _ And Cells(1 + i, 11).Text < "" Then .additem cells(i,"A").value .list(.listindex-1) = cells(i,"B") End If Wend end with End Sub (Untested, uncompiled. Watch for typos.) Eddie_SP wrote: I tried this: Private Sub UserForm_Initialize() Dim i As Integer Dim ComboRange As Range i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then ComboRange = Range("A" & i & ":B" & i) With ComboBox1 .ColumnWidths = "50;80" .AddItem ComboRange End With End If Wend End Sub But it doesn't work... =( -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Try this and see if it loads the Combobox. Private Sub UserForm_Initialize() Dim i As Long, v As Variant Dim rng As Range, lr As Long lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ActiveSheet.Range("A5:A" * lr) ReDim v(0 To rng.Count - 1, 0 To 1) i = -1 For Each c In rng i = i + 1 v(i, 0) = c.Text v(i, 1) = c.Offset(,1).Text Next Combobox1.ColumnCount = 2 Combobox1.List = v Combobox1.BoundColumn = 0 End Sub "Eddie_SP" wrote in message ... I tried this: Private Sub UserForm_Initialize() Dim i As Integer Dim ComboRange As Range i = 5 While (ActiveSheet.Cells(1 + i, 11) < 0) i = i + 1 If Cells(1 + i, 11).Text < "OK" And Cells(1 + i, 11).Text < "" Then ComboRange = Range("A" & i & ":B" & i) With ComboBox1 .ColumnWidths = "50;80" .AddItem ComboRange End With End If Wend End Sub But it doesn't work... =( |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Hi Dave,
One error: Runtime Error 380 - Could not set the list property. Invalid property array index. Do you know what that means? Thank you. Eddie ! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Another typo:
..List(.ListCount - 1, 1) = Cells(i, "B").Text (.listcount, not .listindex) Sorry. And make sure you're not using the .rowsource -- either in code or in the properties menu. Eddie_SP wrote: Hi Dave, One error: Runtime Error 380 - Could not set the list property. Invalid property array index. Do you know what that means? Thank you. Eddie ! -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Woooooooowww !!!
Great great ! In portuguese here, I just said: "Que doideira !!!" hehehe My boss looked at me... =) Thank you Mr. !!! "Dave Peterson" wrote: Another typo: ..List(.ListCount - 1, 1) = Cells(i, "B").Text (.listcount, not .listindex) Sorry. And make sure you're not using the .rowsource -- either in code or in the properties menu. Eddie_SP wrote: Hi Dave, One error: Runtime Error 380 - Could not set the list property. Invalid property array index. Do you know what that means? Thank you. Eddie ! -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Hi Dave,
I just made a correction: While (ActiveSheet.Cells(1 + i, 11).Value < 0) i = i + 1 If UCase(Cells(1 + i, 11).Text) < "OK" And Cells(1 + i, 11).Text < "" Then .AddItem Cells(i + 1, 1).Text 'I ADDED "+ 1" .List(.ListCount - 1, 1) = Cells(i + 1, 2).Text 'I ADDED (+ 1) End If Wend ( + 1) - Because the other way, it adds the value of the upper row, not the row I wanted ! =) Dave, I really really appreciate your help ! Thank you very much ! And thank you JLGWhiz ! Eddie. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox - Problem on AddItem
Glad you got it working!
Eddie_SP wrote: Hi Dave, I just made a correction: While (ActiveSheet.Cells(1 + i, 11).Value < 0) i = i + 1 If UCase(Cells(1 + i, 11).Text) < "OK" And Cells(1 + i, 11).Text < "" Then .AddItem Cells(i + 1, 1).Text 'I ADDED "+ 1" .List(.ListCount - 1, 1) = Cells(i + 1, 2).Text 'I ADDED (+ 1) End If Wend ( + 1) - Because the other way, it adds the value of the upper row, not the row I wanted ! =) Dave, I really really appreciate your help ! Thank you very much ! And thank you JLGWhiz ! Eddie. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AddItem to Worksheet ComboBox | Excel Programming | |||
Combobox.additem (No Repeats) | Excel Programming | |||
additem to combobox with an array | Excel Discussion (Misc queries) | |||
combobox additem | Excel Programming | |||
AddItem Method - Combobox value | Excel Programming |