用户自定义列表框的排序功能(升序)Excel
问题描述:
我是一个IT爱好者,但我不太擅长编程或VBA。作为一个侧面项目,我正在编写一些数据,并希望使用户友好。我是新来的论坛,所以任何建议都会受到欢迎。用户自定义列表框的排序功能(升序)Excel
我有一个用户窗体,有一个列表框,它有一个大量的城市列表,但列表是未排序的。我知道我可以进入最后一页,我有国家资本列表连接到列表框,并直接在工作表中对列进行排序,但那会毁了我的国家列表,所以我想对用户表单列表框中的列表进行排序, 有没有办法做到这一点?
我也希望能够在Userform本身内添加一个Userform'find'函数,因为我已经这样做了,但我不确定如何使它工作,尽管尝试了一些代码,但我失败了,如果你确实知道,那么听到任何类型的建议都会很棒,谢谢您。
请在下面的链接中找到描述目标和我目前拥有的代码的图片。
文件:
https://www.sendspace.com/file/d4iaui
Sub Listb(target)
Location.ListBox1.List = Range("countrycapital").Value
For j = 0 To Location.ListBox1.ListCount - 1
Location.ListBox1.Selected(j) = False
Next j
currentrow = target.Row
'Location.Cells(19, 2) = Sheets("Practice List").Cells(target.Row, 3)
locval = target & ","
k = 0
For i = 1 To Len(locval)
Length = Abs(k - Application.WorksheetFunction.Search(",", locval, i))
Values = Mid(locval, i, Length - 1)
For j = 0 To Location.ListBox1.ListCount - 1
If Location.ListBox1.List(j) = Values Then
Location.ListBox1.Selected(j) = True
GoTo nxt
End If
Next j
nxt:
i = Application.WorksheetFunction.Search(",", locval, i)
k = i
Next i
Location.Show
End Sub
Sub newlocation()
Location.ListBox1.List = Range("countrycapital").Value
For j = 0 To Location.ListBox1.ListCount - 1
Location.ListBox1.Selected(j) = False
Next j
Location.Show
End Sub
Private Sub CommandButton1_Click()
Call ThisWorkbook.checkcriteria
End Sub
Private Sub CommandButton2_Click()
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Initialize()
Dim vaItems As Variant
Dim i As Long, j As Long
Dim vTemp As Variant
Me.ListBox1.AddItem "B" 'these new added values show on the userform
Me.ListBox1.AddItem "A" ' instead, I would like the original Listbox1...
Me.ListBox1.AddItem "D" ' ...incorporated within the sort function
Me.ListBox1.AddItem "C"
'Put the items in a variant array
vaItems = Me.ListBox1.List
'Steal code from John Walkenbach’s Excel Power Programming
'with VBA to sort the array
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 0) > vaItems(j, 0) Then
vTemp = vaItems(i, 0)
vaItems(i, 0) = vaItems(j, 0)
vaItems(j, 0) = vTemp
End If
Next j
Next i
'Clear the listbox
Me.ListBox1.Clear
'Add the sorted array back to the listbox
For i = LBound(vaItems, 1) To UBound(vaItems, 1)
Me.ListBox1.AddItem vaItems(i, 0)
Next i
End Sub
Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Location, Location.ListBox1
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
UnhookListBoxScroll
End Sub
答
我的2美分: - 为了理清,我通常使用.NET排序功能。有些可以通过Com Wrapper访问:CreateObject(“System.Collections.ArrayList”) - 该对象具有.Contains功能,可以使用Find功能。 希望这有助于!
请在您的帖子中包含所有相关代码,并且**不要**仅包含指向托管网站的链接,特别是不要将您的代码作为图片。 – 2015-03-02 05:25:54
内森,感谢您的建议:图片不是代码,图片显示了用户表单和我的主要目标是什么。我会发布相关的代码,看看你是否有一些时间来看看它。再次感谢 – phenomenarchi 2015-03-02 06:08:24
对存储位置的范围进行排序并不容易? – 2015-03-02 07:23:10