Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
I tried that and I got a 'permission denied' error.
" wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
I cleared the rowsource property for the listbox and I still get the
'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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. "ranswrt" wrote: I cleared the rowsource property for the listbox and I still get the 'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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" wrote: I cleared the rowsource property for the listbox and I still get the 'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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" wrote: 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" wrote: I cleared the rowsource property for the listbox and I still get the 'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
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" wrote: 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" wrote: 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" wrote: I cleared the rowsource property for the listbox and I still get the 'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
add to listbox
Thanks for your help I am redoing my code and trying something different.
"JLGWhiz" wrote: 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" wrote: 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" wrote: 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" wrote: I cleared the rowsource property for the listbox and I still get the 'permission denied error. Here is the whole procedu 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 "Bob Phillips" wrote: 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" wrote in message ... I tried that and I got a 'permission denied' error. " wrote: 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 On 25 Apr., 13:46, ranswrt wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |