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
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