vba 如何上下移动listBox中的项目访问VBA？

How to move item in listBox up and down access VBA?

提问by sam

I have a list box contains 10 times . I have UP and Down buttons to move item up and down. My VBA works only if i set listbox multiselect property to 'None'. For the multiselect=simple option it throws error like in valid use of null in this line of code

我有一个包含 10 次的列表框。我有向上和向下按钮来上下移动项目。我的 VBA 仅在我将列表框多选属性设置为“无”时才有效。对于 multiselect=simple 选项，它会抛出错误，例如在这行代码中有效使用 null

     sText = lbfNames.Column(0, iIndex)

My VBA

我的 VBA

    Private Sub cmdUP_Click() 
    Dim sText As String
       Dim iIndex As Integer
       iIndex = lbfNames.ListIndex
       'check: only proceed if there is a selected item
       If lbfNames.ListCount > 1 Then
         'index 0 is top item which can't be moved up!
        If iIndex <= 0 Then
            MsgBox ("Can not move the item up any higher.")
            Exit Sub
        End If
        ' If iIndex = -1 Or lbfNames.ListCount > 1 Then
        'save items text and items indexvalue
        sText = lbfNames.Column(0, iIndex)
        lbfNames.RemoveItem iIndex
        'place item back on new position
        lbfNames.AddItem sText, iIndex - 1
        'if you keep that item selected
        'you can keep moving it by pressing cmdUp
        lbfNames.Selected(iIndex - 1) = True
        iIndex = iIndex - 1
   End If
   End sub

And i was trying to convert the below C# code(found in stackoverflow) to Access VBA throwing errors. Some data members not found.

我试图将下面的 C# 代码（在 stackoverflow 中找到）转换为 Access VBA 抛出错误。未找到某些数据成员。

     public void MoveUp()
 {
     MoveItem(-1);
 }

 public void MoveDown()
 {
    MoveItem(1);
 }

 public void MoveItem(int direction)
 {
    // Checking selected item
    if (listBox1.SelectedItem == null || listBox1.SelectedIndex < 0)
        return; // No selected item - nothing to do

    // Calculate new index using move direction
    int newIndex = listBox1.SelectedIndex + direction;

    // Checking bounds of the range
    if (newIndex < 0 || newIndex >= listBox1.Items.Count)
        return; // Index out of range - nothing to do

    object selected = listBox1.SelectedItem;

    // Removing removable element
    listBox1.Items.Remove(selected);
    // Insert it in new position
    listBox1.Items.Insert(newIndex, selected);
    // Restore selection
    listBox1.SetSelected(newIndex, true);
}

Is there anyway to do this in access vba.

无论如何在访问 vba 中执行此操作。

采纳答案by Yoh

I actually reconstructed this setting but could never get the error you mentioned. I did play around with the code to adjust it to what you were trying to do. Try this:

我实际上重建了这个设置，但永远无法得到你提到的错误。我确实使用了代码以将其调整为您尝试执行的操作。尝试这个：

Private Sub cmdup_Click()
Dim sText As String
Dim iIndex As Variant
Dim selection() As Integer
Dim n, topSelection As Integer

' save the indexes of the selected items,
' they will be deselected after the first removal
For Each iIndex In lbfnames.ItemsSelected
    ReDim Preserve selection(0 To n)
    selection(n) = iIndex
    n = n + 1
Next

'loop through all the selected indexes
'this will also ensure you will only proceed if there is a selected item
For n = LBound(selection) To UBound(selection)
    'save items text and items indexvalue
    sText = lbfnames.Column(0, selection(n))

    If selection(n) <= topSelection Then 'index topSelection is top item which can't be moved up!
        MsgBox ("Can not move item '" & sText & "' up any higher.")
        topSelection = topSelection + 1
    Else
        'first remove item from old position
        lbfnames.RemoveItem selection(n)
        'place item back on new position
        lbfnames.AddItem sText, selection(n) - 1
        'change the index of the selected value to the new index (for reselection)
        selection(n) = selection(n) - 1
    End If
Next
'loop through the selection again to reselect
For n = LBound(selection) To UBound(selection)
    lbfnames.Selected(selection(n)) = True
Next
End Sub

The code and comments are self-explanatory I think, but here is a quick run-through:

我认为代码和注释是不言自明的，但这里有一个快速运行：

1. I save the selected elements indexes first because I noticed after the removal/addition of the element that the selection was gone.
2. I then run through this selection, I reused your code here. Changed the condition for the pop up message, because if you select the top 2 elements for example (say 1 and 2), you don't want to only get the messagebox for 1 and then in the next loop put 2 ahead of 1. (Unless that is what you want, then change this condition back to 0)
3. Add the end I loop through the selected elements a second time to select them again for moving them further up the list.

1. 我首先保存所选元素索引，因为我注意到在删除/添加元素后选择消失了。
2. 然后我运行了这个选择，我在这里重用了你的代码。更改了弹出消息的条件，因为如果您选择顶部 2 个元素（例如 1 和 2），您不想只获取 1 的消息框，然后在下一个循环中将 2 放在 1 之前。 （除非这是你想要的，然后将此条件改回 0）
3. 添加结尾 I 再次遍历所选元素以再次选择它们以将它们进一步向上移动到列表中。

Note: the example C# code shows a more generic function for both directions of movement. I did not adapt that, I think that is a good idea but leave it to you to implement (always a good exercise to understand the code).

注意：示例 C# 代码显示了用于两个移动方向的更通用的函数。我没有适应它，我认为这是一个好主意，但留给你实现（总是一个很好的理解代码的练习）。

回答by nimrod1313

The following code is type safe for direction by using an enum. The solution allows movement up and down the list. The solution wraps the movement (e.g. if at top of list and attempting to move up it wraps the item to the bottom.

通过使用枚举，以下代码对于方向而言是类型安全的。该解决方案允许在列表中上下移动。该解决方案包装移动（例如，如果在列表顶部并尝试向上移动，它将项目包装到底部。

Private Enum directions
    down = -1
    up = 1
End Enum

Private Sub cmdDown_Click()
    moveListItem (down)
End Sub

Private Sub cmdMvUp_Click()
    moveListItem (up)
End Sub

Private Sub moveListItem(direction As directions)
    With Me.ListBox1
        Select Case .ListIndex

            ' at bottom and moving down then wrap around to top
        Case Is >= .ListCount + direction
            .AddItem .Column(0, .ListCount - 1), 0
            .RemoveItem (.ListCount - 1)
            .Selected(0) = True

            ' at top and moving up then wrap around to bottom
        Case Is < direction
            .AddItem .Column(0, 0), .ListCount
            .RemoveItem (0)
            .Selected(.ListCount - 1) = True

        Case Else
            .AddItem .Column(0, .ListIndex - direction), .ListIndex + ((direction + 1) / 2)
            .RemoveItem (.ListIndex - direction)
        End Select

    End With
End Sub

回答by nimrod1313

if you don't want the wrap around feature modify the above solution to this ..

如果您不希望环绕功能将上述解决方案修改为此..

Option Explicit
Private Enum directions
    down = -1
    up = 1
End Enum

Private Sub cmdDown_Click()
    moveListItem (down)
End Sub

Private Sub cmdMvUp_Click()
    moveListItem (up)
End Sub

Private Sub moveListItem(direction As directions)
    With Me.ListBox1
        Select Case .ListIndex
            ' at bottom and moving down then wrap around to top
        Case Is >= .ListCount + direction

            ' at top and moving up then wrap around to bottom
        Case Is < direction

        Case Else
            .AddItem .Column(0, .ListIndex - direction), .ListIndex + ((direction + 1) / 2)
            .RemoveItem (.ListIndex - direction)
        End Select
    End With
End Sub