PDA

View Full Version : Solved: ListBox Question



Pizzafiend
12-24-2005, 01:34 PM
I am trying to transfer an item picked from a list box to a cell on a spreadsheet. Every time a different item is picked it still only transfers the first item. How can I ensure that the next item picked will transfer when my submit button is clicked?


Private Sub cmdSubmitEmp_Click()
Dim LI1 As Integer
Dim LI2 As Integer
Dim LI3 As Integer
Static EmpCellColumn As Integer
Static FuncCellColumn As Integer
Static ShiftCellColumn As Integer

If Me.lstAddEmp.ListIndex = -1 Then MsgBox ("You Must Choose a Name!"): Exit Sub
If Not cbDuplicates Then
For LI1 = 0 To Me.lstSelEmp.ListCount - 1
If Me.lstAddEmp.Value = Me.lstSelEmp.List(LI1) Then
Beep
Exit Sub
End If
Next LI1
End If
Me.lstSelEmp.AddItem Me.lstAddEmp.Value
EmpCellColumn = EmpCellColumn + 1

If Me.lstEmpFunc.ListIndex = -1 Then MsgBox ("You Must Choose a Function!"): Exit Sub
If Not cbDuplicates Then
For LI2 = 0 To Me.lstSelFunc.ListCount - 1
Next LI2
End If
Me.lstSelFunc.AddItem Me.lstEmpFunc.Value
FuncCellColumn = FuncCellColumn + 1

If Me.lstEmpShift.ListIndex = -1 Then MsgBox ("You Must Choose a Shift!"): Exit Sub
If Not cbDuplicates Then
For LI3 = 0 To Me.lstSelShift.ListCount - 1
Next LI3
End If
Me.lstSelShift.AddItem Me.lstEmpShift.Value
ShiftCellColumn = ShiftCellColumn + 1


Call PasteInfo(EmpCellColumn, FuncCellColumn, ShiftCellColumn)

Function PasteInfo(ByRef EmpCellColumn, ByRef FuncCellColumn, ByRef ShiftCellColumn)

With Worksheets("Monthly Figures")
.Cells(25, EmpCellColumn) = Me.lstSelEmp.List
.Cells(27, FuncCellColumn) = Me.lstSelFunc.List
.Cells(29, ShiftCellColumn) = Me.lstSelShift.List
End With


End Function

tpoynton
12-24-2005, 02:03 PM
I would try and work up a sample, but I have family stuff to do...

In a multiselect listbox, you need to store the listbox positions in an array; you then loop through the array to get your data out of it.

again, sorry i dont have time to work up a sample, but I believe that is the issue...

EDIT - ALSO: having a sample worksheet would make it easier for people to help you...

Bob Phillips
12-24-2005, 07:28 PM
Haven't tested it, but shouldn't


With Worksheets("Monthly Figures")
.Cells(25, EmpCellColumn) = Me.lstSelEmp.List
.Cells(27, FuncCellColumn) = Me.lstSelFunc.List
.Cells(29, ShiftCellColumn) = Me.lstSelShift.List
End With


be replaced with


With Worksheets("Monthly Figures")
.Cells(25, EmpCellColumn) = Me.lstSelEmp.Value
.Cells(27, FuncCellColumn) = Me.lstSelFunc.Value
.Cells(29, ShiftCellColumn) = Me.lstSelShift.Value
End With

Pizzafiend
12-24-2005, 07:40 PM
I tried it, but if I use .Value then it doesn't put anyhting in the cells.

Pizzafiend
12-24-2005, 08:23 PM
I think I have it figured it out. Thanks anyway