add to listbox

[ranswrt]

I have a form with a listbox with 7 columns and I want add items to the
listbox using textboxes and a commandbutton. when I click the commandbutton
to add the items in the textboxes to the end of the items in the listbox I
get an error. the code that I have tried is :

For i = 0 To 6
With ListBox1
.List(num, i) = aitem(num, i)
End With
Next

I get a 'type mismatch error.
What is the best way to enter new items to the end of an existing list of
items in a listbox that has 7 columns?
Thanks

 

[steonken]

hi ranswrt,
you need AddItem to create a new row in the listbox. try

With ListBox1
.AddItem
For i = 0 To 6
.List(num, i) = aitem(num, i)
Next
End With

stefan

 

[ranswrt]

I tried that and I got a 'permission denied' error.

 

[Bob Phillips]

Have you got the ListFillRange/RowSource property set in the design mode? If
so, clear it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ranswrt]

I cleared the rowsource property for the listbox and I still get the
'permission denied error.

Here is the whole procedure:

Private Sub CommandButton11_Click()
Dim aitem(6) As Variant
Dim item As String
Dim num As Integer
Dim xcell As Range
Dim ycell As Range
Dim rng As Range
Dim i As Integer
Dim cntr As Integer

item = Range("currentdb")
item = LCase(item)
aitem(0) = TextBox1.Value
aitem(1) = ComboBox1.Value
aitem(2) = 0
aitem(3) = 0
aitem(4) = 0
aitem(6) = 0
num = Range(item & "itemnum")
cntr = 0
Set xcell = Range(item & "Itemno")
For i = 1 To num
Set ycell = xcell.Offset(i, 1)
If ycell.Value = aitem(0) Then
cntr = cntr + 1
End If
Next
If cntr = 0 Then
If aitem(0) = "" Then
MsgBox ("Enter New Database Item.")
updateDB.TextBox1.SetFocus
Else
If aitem(1) = "" Then
MsgBox ("Select Unit for New Database Item.")
updateDB.ComboBox1.SetFocus
Else
With ListBox1
.AddItem
For i = 0 To 6
.List(num, i) = aitem(i)
Next
End With

Label8.caption = num + 1
End If
End If
Else
MsgBox ("Item '" & aitem(0) & "' already exists, select another item.")
updateDB.TextBox1.SetFocus
End If



End Sub

 

[JLGWhiz]

If you set your initial ListBox values from a RowSource or LIstFill range,
then VBA will not let you use AddItem to modify it because there is no
facility to change the RowSource or ListFill range. Other than using the
AddItem method to initially load your ListBox, I don't know what other
solutions might be available. Maybe one of the Pros can shed some light.

 

[JLGWhiz]

There is an alternative. You can add to the RowSource range if you have the
room on the sheet to add another row and then modify your ListBox rowsource
reference accordingly.

 

[ranswrt]

Originally I would load the value into the listbox from a worksheet. When I
would add new values to the worksheet with the userform, I would then reload
the values from the worksheet into the listbox. The problem I would have is
any items that were selected in the listbox before the new item was added
were lost. I thought I could use 'additem' to the bottom of the list without
changing the items that were selected. Is there a way I can do that?
Thanks

 

[JLGWhiz]

I don't believe you can add a new item without loosing focus on the selected
items, since the added item automatically gets the focus. You would have to
re-select the items anyhow. You can't have simultaneous events of select,
add and delete. When you go to a new event the old one loses focus.

 

[ranswrt]

Thanks for your help I am redoing my code and trying something different.