add to listbox

R

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
 
S

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
 
B

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)
 
R

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
 
J

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

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

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
 
J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top