I think the attached will do what you want.
Each list box holds both names, but with a different column hidden.
The SortListBox sub will sort a list box by the column indicated (defaults to column 0)
The ListBoxes are connected via Change event so that the selecting a name in one list box will select the matching name in the other list box.
Pressing the added button will move the selected name to the top of both list boxes.
' in userform code module
Dim DisableMyEvents As Boolean
Private Sub box_first_Click()
If DisableMyEvents Then Exit Sub
DisableMyEvents = True
box_last.ListIndex = box_first.ListIndex
DisableMyEvents = False
End Sub
Private Sub box_last_Click()
If DisableMyEvents Then Exit Sub
DisableMyEvents = True
box_first.ListIndex = box_last.ListIndex
DisableMyEvents = False
End Sub
Private Sub CommandButton1_Click()
Call MoveToTop
End Sub
Private Sub CommandButton2_Click()
SortListbox box_first, 0
SortListbox box_last, 0
End Sub
Private Sub CommandButton3_Click()
SortListbox box_first, 1
SortListbox box_last, 1
End Sub
Private Sub UserForm_Initialize()
With box_first
.ColumnCount = 2
.ColumnWidths = ";0"
.List = Range("B6:C13").Value
End With
With box_last
.ColumnCount = 2
.ColumnWidths = "0;"
.List = Range("B6:C13").Value
End With
End Sub
Sub MoveToTop()
Dim FirstName As String
Dim SecondName As String
With box_first
If .ListIndex <> -1 Then
FirstName = .List(.ListIndex, 0)
SecondName = .List(.ListIndex, 1)
box_last.RemoveItem .ListIndex
.RemoveItem .ListIndex
.AddItem FirstName, 0
.List(0, 1) = SecondName
box_last.AddItem FirstName, 0
box_last.List(0, 1) = SecondName
.ListIndex = 0
End If
End With
End Sub
Sub SortListbox(ListBox As MSForms.ListBox, Optional SortColumn As Long = 0)
Dim ItemCount As Long
Dim currentItem As Long
Dim i As Long, j As Long
DisableMyEvents = True
With ListBox
ItemCount = .ListCount
For currentItem = 0 To ItemCount - 1
For i = ItemCount To .ListCount - 1
If LCase(.List(currentItem, SortColumn)) < LCase(.List(i, SortColumn)) Then Exit For
Next i
.AddItem .List(currentItem, 0), i
For j = 1 To .ColumnCount - 1
.List(i, j) = .List(currentItem, j)
Next j
If currentItem = .ListIndex Then .ListIndex = i
Next currentItem
For i = 0 To ItemCount - 1
.RemoveItem 0
Next i
End With
DisableMyEvents = False
End Sub
Bookmarks