ListBox values not changing to specified range

[MarkMagic]

Hi all,

I have a worksheet ("Team1") that lists 19 football players in cells C2:20, each with a command button next to it.

I then have a list of formations that the team can play in, in cells DL2:DL34 (this is in a table). Columns DM:EE in this table show the 19 positions that make up the formation (11 positions, 7 substitutes, 1 reserve). For example, row 2 in this table reads as follows, from column DL to EE:

4-4-2 | Goalkeeper | Right-back | Right centre-back | Left centre-back | Left-back | Right midfield | Right centre-midfield | Left centre-midfield | Left midfield | Right striker | Left striker | Sub1 | Sub2 | Sub3 | Sub4 | Sub5 | Sub6 | Sub7 | Reserve

In Cell CX2 I have a drop-down menu with all the formations (DL2:DL34).

The idea is that a user selects a formation from this drop-down menu (e.g. 4-4-2) and then can start assigning players to the positions in their chosen formation by clicking the command button next to each player.


I have created a UserForm called PosList that contains a ListBox (ListBox1) that should be populated by the positions in columns DM:EE depending on what formation has been selected in cell CX2.

My code for the UserForm:

Private Sub UserForm_Initialize()
Dim Forma As String, Posi As Range
Dim i As Integer, lcount As Long


Forma = Team1.Range("CX2")


Select Case Forma                   'I imagine there is a better way of doing this bit...
    Case Team1.Range("DL2")
        i = 2
    Case Team1.Range("DL3")
        i = 3
    Case Team1.Range("DL4")
        i = 4
    Case Team1.Range("DL5")
        i = 5
    Case Team1.Range("DL6")
        i = 6
    Case Team1.Range("DL7")
        i = 7
    Case Team1.Range("DL8")
        i = 8
    Case Team1.Range("DL9")
        i = 9
    Case Team1.Range("DL10")
        i = 10
    Case Team1.Range("DL11")
        i = 11
    Case Team1.Range("DL12")
        i = 12
    Case Team1.Range("DL13")
        i = 13
    Case Team1.Range("DL14")
        i = 14
    Case Team1.Range("DL15")
        i = 15
    Case Team1.Range("DL16")
        i = 16
    Case Team1.Range("DL17")
        i = 17
    Case Team1.Range("DL18")
        i = 18
    Case Team1.Range("DL19")
        i = 19
    Case Team1.Range("DL20")
        i = 20
    Case Team1.Range("DL21")
        i = 21
    Case Team1.Range("DL22")
        i = 22
    Case Team1.Range("DL23")
        i = 23
    Case Team1.Range("DL24")
        i = 24
    Case Team1.Range("DL25")
        i = 25
    Case Team1.Range("DL26")
        i = 26
    Case Team1.Range("DL27")
        i = 27
    Case Team1.Range("DL28")
        i = 28
    Case Team1.Range("DL29")
        i = 29
    Case Team1.Range("DL30")
        i = 30
    Case Team1.Range("DL31")
        i = 31
    Case Team1.Range("DL32")
        i = 32
    Case Team1.Range("DL33")
        i = 33
    Case Team1.Range("DL34")
        i = 34
End Select


For lcount = 117 To 135                 '117 is column DM
    ListBox1.AddItem Cells(i, lcount)
Next
End Sub

This seems to work the first time I use it but then if I change the formation in CX2 and run the UserForm again (by clicking a command button next to a player), the ListBox has not updated to match the new formation.

Any thoughts on why this is?