Userform ListBox customer ordering with VBA issue

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
379
Hi


I have a couple of list boxes, Listbox1 is populated when the Userform is opened and the order is based on usability, higher usability at the top and lower at the bottom. Example code below

VBA Code:
With Me.ListBox1
            .AddItem "g1"
            .AddItem "b1"
            .AddItem "h1"
            .AddItem "d1"
            .AddItem "a1"
            .AddItem "f1"
            '.AddItem "e1
            .AddItem "c1"
    End With

Me.ListBox1.MultiSelect = fmMultiSelectMulti

I then have a command button in the UserForm to move selected items from ListBox1 to ListBox2, code below

Code:
Dim iCtr As Long
Dim V As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox1.RemoveItem iCtr
        End If
    Next iCtr

    For V = ListBox2.ListCount - 1 To 0 Step -1
        ListBox2.ListIndex = V
    Next V

Finally I have a second command button in the UserForm to move selected items back from ListBox2 to ListBox1, then last part of the VB code in this button will order ListBox1 in alpha order, which I don’t want, I want it to apply the original order when populated, the only issue with that is not all items will be moved back from ListBox2 to ListBox1 so the original order needs to match with the exception of the missing items? Is this possible? A custom order sort? Code below to move items back

Code:
Dim iCtr As Long
    Dim K As Long
    Dim V As Long
    Dim Temp As Variant

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox2.RemoveItem iCtr
        End If
    Next iCtr

    With ListBox1
        For K = 0 To .ListCount - 2
            For V = 0 To .ListCount - 2
                If LCase(.List(V)) > LCase(.List(V + 1)) Then
                    Temp = .List(V)
                    .List(V) = .List(V + 1)
                    .List(V + 1) = Temp
                End If
            Next V
        Next K
    End With
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MichaelRSnow

Active Member
Joined
Aug 3, 2010
Messages
379
Anyone able to assist with the above?, can't find anything anyway to suggest this may be possible?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Michael

Why not have a second column in each listbox that indicates the original position of each item?

Both columns could be transferred between the listboxes as required and the second used for any sort of ordering that was needed.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes, you can set the it's column width to 0 using the ColumnWidths property.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,265
Messages
5,571,215
Members
412,368
Latest member
saranbl
Top