VBA: how to adapt this listbox code when multiselect is enabled?

[Kimberley]

Hi,

I would like to adapt 2 codes that are working perfectly with a listbox with multiselect disabled. Here are the 2 codes written respectively by MickG and vaskov17 (thanks again):

Private Sub deplacer_haut_Click()

'Move up
Dim temp As String
Dim Ray
Dim ac As Integer
Dim n
With ListBox1
    Ray = .List
    n = .ListIndex
If Not .ListIndex = 0 And Not .Value = vbNullString Then
    For ac = 1 To 2
        temp = .List(.ListIndex, ac)
        Ray(.ListIndex, ac) = Ray(.ListIndex - 1, ac)
        Ray(.ListIndex - 1, ac) = temp
    Next ac
        .List = Ray
        .ListIndex = n - 1
End If
End With

End Sub

Private Sub Effacer_Click()
    Dim i As Integer
    
    With ListBox1
    
    If Not .ListIndex = -1 And Not .Value = vbNullString Then
        .RemoveItem (.ListIndex)
        
        If .ListIndex <> .ListCount - 1 Then
            For i = .ListIndex To .ListCount - 1
                .List(i, 0) = .List(i, 0) - 1
            Next i
        End If
    End If
    End With
End Sub

You can download the .xlsm file there:
UserForm - Multiselect.xlsm - 0.03MB

Thanks in advance for the help!

 

[Kimberley]

It doesn't work because I changed the listbox setting to multiselect instead of multiselectsingle:

With multiselectsingle enabled, the codes should work fine. But when multiselect Extended is enabled (which I would like to), then it doesn't.

It's odd you're getting popups though (can you do a screenshot of what you're getting?), even though the code is not working when multiselect is enabled, it doesn't do anything when I press the corresponding buttons (so no popup).

 

[Norie]

The link for the download didn't work and I ended up with about 3 or 4 unwanted advertising web pages being lanunched.

I'll try again later.

I did come up with something using my own setup but it's robably best to see your file before posting anything.

 

[Kimberley]

Oh ok, we were not talking about the same thing, I thought you were talking about excel generating popups due to code errors

The link is working but that's true there are lots of popups before the download starts. Here is a new link to the file (you have to click on "Click here to start download from sendspace")

http://www.sendspace.com/file/ttpcy5

 

[Norie]

Right, I've got it.

I'll try the code I already have, adapted obviously.

 

[Norie]

Try this, it seems to work but it's only lightly tested.

http://www.box.com/s/st37ijxx90m0jygagcgq

I added the buttons in blue to move multiple items, but they also work for individual items.

PS Only had time to do the up/down buttons.

 

[Kimberley]

Thanks Norie, it's working well. Is it possible to make the number column stay in place? (only the couple names would move up/down).

I also posted on excelforum and pike is helping me with the "delete" button.

 

[Norie]

Kimberley

Why do you have the number column?

I thought it was connected to the item/record so that's why I moved it with the rest of the data.

As for deleting, that's quite straightforward:

Dim I As Long
 
For I = Listbox1.ListCount-1 To 0 Step -1
       If Listbox1.Selected(I) Then
            Listbox1.RemoveItem I
       End If
Next I

 

[Kimberley]

Hi Norie,

I have the number column to create a reference position for each couple, they will get listed by order of importance.

This list will constantly be updated, that's why it's loading the data saved on Sheet2 into the listbox. I can then add entries to the listbox with the textboxes, modify entries with the modify button for data correction, if a name has been badly entered for example, I can also delete entries if some couples have to get removed from this list, delete everything in the list if I need to start the list from the beginning, and move up and down couples by order of importance so a number is not attached to a specific couple. I can then save my new list to Sheet2 when hitting the "Apply" button.

Thanks again for looking into this!

 

[Norie]

Kimberley

Each item in the listbox has an index.

The index has for item 1 is 0, for item 2 is 1, and so on.

For item 1 in your listbox column 1 has 1, for item 2 it has 2, for item 3 it has 3.

Can you see the relationship between the item's index and the value in column 1?

Column 1 value = item index + 1

So as far as I can see you don't really need the first column.

There's also a relationship between the index and the row the data originally come from.

Item 1 which has index 0 comes from row 2
Item 2 which has index 1 comes from row 3
Item 3 which has index 2 comes from row 4
...
Item X has index X-1 and comes from row X+1

I don't know but there might be something useful there.