How can I remove a row in a listbox?

[lokeefe]

Hi all,
Maybe someone can help me. I have 2 list boxes. One is called lstSource and the other is called lstDestination. I am using the lstSource box to populate my lstDestination box. I am able to successfully move data from my source list box over to my empty Destination listbox. However, I want to know how to code my back arrow button so that I can allow the user to remove a row if he or she makes a mistake or changes his or her mind. Any have any ideas? It seems like it should be so simple but I just can't get it! Maybe it is just because it is Friday!

Thanks in advance!

 

[rmikesmith]

lokeefe,

Try the following:

With lstDestination
  If (.ListCount > 0) And (.ListIndex <> -1) Then
    .RemoveItem .ListIndex
  End If
End With

Note: This construct will only work if lstDesination's Multiselect property is set to False; otherwise use must use the Selected property.

HTH
M. Smith

 

[lokeefe]

Thanks for your help,
The code looks good just one problem.... I am getting an error on the .RemoveItem. Is this a data member of VBA? It is saying Method or data member not found (Error 461). I think the code is perfect for what I need if that would just work!

Any suggestions?

 

[rmikesmith]

lokeefe,

I din't think to ask before; what host application are you using? My code is based on the MSForms Listbox control. Sounds like yours is not!

Regards,
M. Smith

 

[lokeefe]

Hi,
Just got your message this morning. I am using Microsoft Access 2000 with Visual Basic for Appications and my control is a Listbox control. Shouldn't this work?

 

[rmikesmith]

lokeefe,

I suspected you might be using Access, so I took a look at this with Access 97, which is probably similar enough to 2000 for this purpose. The ListBox control listed in the Access Object Browser is different than that in Excel (it is a member of Access versus MSForms in Excel), with different properties and methods. In particular, the methods AddItem and RemoveItem do not exist. It appears the only way to get at the items listed is to use the RowSource property. Below is some sample code that works but may not be appropriate, since I assigned RowSource to a literal string (with RowSourceType set to Value List). I'm not sure how you have coded your two ListBoxes to populate one from the other. However, you should be able to set lstDestination's RowSource property at run-time based on the user's selection(s) in lstSource (e.g. place such code in the DblClick event of lstSource). In any case, here is my code, based on a ListBox control (representing lstDestination) and a CommandButton control (representing a back arrow or delete button) on an Access form:

Private Sub Command1_Click()
Dim TmpStr As String
Dim Pos1 As Long, Pos2 As Long

With Me.Lstbox1
  LItems = .RowSource
  'Debug.Print .ColumnCount
  If .ListIndex <> -1 Then
    Pos1 = InStr(1, LItems, .Column(0, .ListIndex), vbTextCompare)
    TmpStr = Mid(LItems, 1, Pos1 - 1)
    Pos2 = InStr(Pos1, LItems, &quot;;&quot;, vbTextCompare)
    If Pos2 = 0 Then
      TmpStr = Mid(TmpStr, 1, Len(TmpStr) - 1)
    Else
      TmpStr = TmpStr & Mid(LItems, Pos2 + 1)
    End If
    LItems = TmpStr
    .RowSource = LItems
  End If
End With

End Sub

Post back with any questions or to let others know if this has helped.

M. Smith