How to number listbox items, enable a cmd button and move item lines?

[Kimberley]

Hi,

I'm creating a userform and there are some things I'd like to do in order to improve it but I don't know how to.

I would like first to number each listbox item (using ListBox1.ListCount I guess), so it would appear like this:

01   Todd	Britney
02   Jimi	Meredith
03   Serge	Amy
04   Richard	Kim
05   Roger	Gabrielle
06   Mike	Tammy
07   Bill	Jill
08   Jim	Joyce
09   Phil	Judy
10   Bebel	Jules
11   Fredrik	Helen
12   James	Yolanda
...

I'm also trying to be able to move lines within the listbox. The code as it is now only allows to move an item (not the item and its whole line). It would only moves the couple names, not its affiliated number. So if I were to move Mike + Tammy up of one line, they would now be couple #05 and Roger + Gabrielle would then be couple #06.

Also I would like to enable the Delete button when an item in the listbox is selected.

Thanks in advance for your help!

UserForm.xlsm - 0.03MB

 

[bensonsearch]

Hi,
You can use the listbox index to move them. so if mike is listindex 2 and you make it 1 then it moves up.

you could make the number a second listbox the displays the listindex

 

[Kimberley]

Hi, thanks but I'm not sure how to do so. I would like to avoid creating a second listbox for clarity purpose.

 

[MickG]

Set you list out in range "A1:C12" then using the Initialize Event to fill the List box as below.
The code below assumes 3 columns , Alter to suit.

With ListBox1
 .Clear
 .ColumnCount = 3
 .List = Range("A1:C12").Value
 End With

Use the Two codes below in Userform/CommandButtons to move selected data up and down the list a row a a time:-

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

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

Mick

 

[Kimberley]

Thanks Mick it's working great, but is it possible to freeze the numbers column (ie the first one) when moving a row up or down? (cf 1st post : "It would only moves the couple names, not its affiliated number. So if I were to move Mike + Tammy up of one line, they would now be couple #05 and Roger + Gabrielle would then be couple #06. ")

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] CommandButton3_Click()
'[COLOR="Green"][B]Move Down[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] ListBox1
    n = .ListIndex
    Ray = .List
[COLOR="Navy"]If[/COLOR] Not .ListIndex = .ListCount - 1 And Not .Value = vbNullString [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] ac = 1 To 2
        temp = .List(.ListIndex, ac)
        Ray(.ListIndex, ac) = Ray(.ListIndex + 1, ac)
        Ray(.ListIndex + 1, ac) = temp
    [COLOR="Navy"]Next[/COLOR] ac
        .List = Ray
        .ListIndex = n + 1
 [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] CommandButton4_Click()
'[COLOR="Green"][B]Move Up[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n
[COLOR="Navy"]With[/COLOR] ListBox1
    Ray = .List
    n = .ListIndex
[COLOR="Navy"]If[/COLOR] Not .ListIndex = 0 And Not .Value = vbNullString [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] ac = 1 To 2
        temp = .List(.ListIndex, ac)
        Ray(.ListIndex, ac) = Ray(.ListIndex - 1, ac)
        Ray(.ListIndex - 1, ac) = temp
    [COLOR="Navy"]Next[/COLOR] ac
        .List = Ray
        .ListIndex = n - 1
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kimberley]

Thanks Mick, it's working perfectly.

One more thing, I'm using this code to delete lines:

Private Sub Effacer_Click()

    If Not ListBox1.ListIndex = 0 And Not ListBox1.Value = vbNullString Then
        ListBox1.RemoveItem (ListBox1.ListIndex)
    End If
    
End Sub

It's working great except that it also removes the number of the first column. So how to delete the selected row without removing its associated number?

Example: if I delete couple #2, Serge + Amy would now be couple #2, Richard + Kim would become couple #3 [..] James + Yolanda would become couple #11 and the #12 would get deleted.

01   Todd	Britney
02   Serge	Amy
03   Richard	Kim
04   Roger	Gabrielle
05   Mike	Tammy
06   Bill	Jill
07   Jim	Joyce
08   Phil	Judy
09   Bebel	Jules
10   Fredrik	Helen
11   James	Yolanda

Or maybe, as soon as the Delete button is pressed, a new ListCount is done.

 

[MickG]

Perhaps this:-

Private Sub Effacer_Click()
With ListBox1
If Not .ListIndex = 0 And Not .Value = vbNullString Then
       .List(.ListIndex, 1) = vbNullString
          .List(.ListIndex, 2) = vbNullString
    End If
End With
End Sub

 

[Kimberley]

Thanks Mick, that's not exactly what I wanted, but I'll create another thread to avoid getting things mixed up, so feel free to chime in if you've got the time.