I've run into an issue that I am having a difficult time figuring out so any help will be appreciated.

Here's the story: I have two ListBoxes (lstMGR and lstTeam). I am trying to create a way that multiple teams can be assigned to a MGR by selecting the MGR and then selecting their assigned teams from the respective ListBoxes. I thought using an array would be a good way to store the team numbers (since each MGR can have up to 20 assigned to them) and used this code ...

Dim intRow As Integer
Dim strTeamNumber(0 To 99) As String

With Forms("frmLeadership").lstTeam
   For intRow = 0 To .ListCount
      If .Selected(intRow) = True Then
         strTeamNumber(intRow) = .Column(0)
         Debug.Print strTeamNumber(intRow)
      End If
   Next intRow
End With

This appears to work fine with one exception. If I choose team 001, 003, 005, and 007 for a MGR the array is loaded with the data from the last selected line in lstTeam (ie 005, 005, 005, 005 is the result).

My question is how do I get each selected row of data loaded into a separate array element?

Kenny Alligood

__________________
Kenny Alligood

Kenny,
   Change the code .Column(0) to

   .itemdata(intRow)

   You could change it to use the Hidden ItemsSelected Collection

   Dim varItem as Variant
   Dim intCount as integer
   Dim strTeamNumber(0 TO 99) as String

   For Each varItem in Me.lstTeam
      strTemNumber(intCount) = me.lstTeam.ItemData(varItem)
      intCount = intCount+1
      debug.print strTeamNumber(intcount)
   Next

Hope this helps,
Mike

forgot one thing,

Change Me.lstTeam to Me.lstTeam.ItemsSelected

Mike

Thanx Mike -- I really appreciate your assistance! It appears that your plan worked but the value was not being assigned to the array element; so I'm going to change my plan. Here is how the code works...

Dim varItem As Variant
Dim strTeamNumber As String

With Forms("frmLeadership").lstTeam
   For Each varItem In .ItemsSelected
      strTeamNumber = .ItemData(varItem)
      Debug.Print strTeamNumber
   Next varItem
End With

I actually have this in a module therefore I can't us the Me keyword (in case you were wondering). What I plan to do now is use the value of strTeamNumber and .Update the rst from within the loop until all teams are assigned. I needed to .Update the rst anyway so this should work fine. Again, thanx for your help.

Kenny Alligood

Kenny,
  Glad I could help. One final comment. You can use the Class object name in a standard module...this gives you the benefit of Intellisense.

   Instead of using Forms("frmLeadership").lstTeam

   use

   Form_frmLeadership.lstTeam

   Notice that when you type the period, you get the intellisence help.

Regards,
Mike

Another great tip -- thanx Mike!

Kenny Alligood