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.

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" <(E-Mail Removed)> wrote in message
news:B28DD8B7-CE92-428E-8E55-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
news:B28DD8B7-CE92-428E-8E55-(E-Mail Removed)...
> 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.

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...

=(

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

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

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" <(E-Mail Removed)> wrote in message
news:6654B3AC-BD3F-4D01-8482-(E-Mail Removed)...
>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...
>
> =(

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 !

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

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
>