Hi GUys,
I need help.. i have a userform that contains 1 combobox and 2 listboxes that everytime i choose a category in my combobox, datas will appear in both listboxes depending on its status.. but my problem is, the column from which the status are in my worksheet should update from pending to linedup after i transfer the selected item from listbox2 into listbox1..
pls help me..
here is the full code of the userform;
I need help.. i have a userform that contains 1 combobox and 2 listboxes that everytime i choose a category in my combobox, datas will appear in both listboxes depending on its status.. but my problem is, the column from which the status are in my worksheet should update from pending to linedup after i transfer the selected item from listbox2 into listbox1..
pls help me..
here is the full code of the userform;
VBA Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Dim x, lastrow As Long
Dim mysum As Double
ListBox1.clear
ListBox2.clear
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox1.SetFocus
TextBox2.SetFocus
TextBox3.SetFocus
TextBox4.SetFocus
If ComboBox1.Value = "CREASING" Then
'creasing
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "CREASING" Or _
Sheet7.Cells(x, "AL").Value = Val("CREASING") Then
If Sheet7.Cells(x, "AM").Value = "LINED-UP" Or _
Sheet7.Cells(x, "AM").Value = Val("LINED-UP") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
If Sheet7.Cells(x, "AL").Value = "CREASING" Or _
Sheet7.Cells(x, "AL").Value = Val("CREASING") Then
If Sheet7.Cells(x, "AM").Value = "PENDING" Or _
Sheet7.Cells(x, "AM").Value = Val("PENDING") Then
With Me.ListBox2
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 22) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 22) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
Next
On Error Resume Next
Dim d As Integer
Dim e As Integer
Dim urlist As Variant
With Me.ListBox1
For d = 1 To .ListCount - 1
For e = 1 To .ListCount - 1
If CDate(.List(e, 5)) > CDate(.List(d, 5)) Then
For c = 0 To 6
urlist = .List(e, c)
.List(e, c) = .List(d, c)
.List(d, c) = urlist
Next c
End If
Next e
Next d
End With
With Me.ListBox2
For d = 1 To .ListCount - 1
For e = 1 To .ListCount - 1
If CDate(.List(e, 5)) > CDate(.List(d, 5)) Then
For c = 0 To 6
urlist = .List(e, c)
.List(e, c) = .List(d, c)
.List(d, c) = urlist
Next c
End If
Next e
Next d
End With
End If
'---
If ComboBox1.Value = "PRINTING" Then
'printing
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "PRINTING" Or _
Sheet7.Cells(x, "AL").Value = Val("PRINTING") Then
If Sheet7.Cells(x, "AM").Value = "LINED-UP" Or _
Sheet7.Cells(x, "AM").Value = Val("LINED-UP") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
If Sheet7.Cells(x, "AL").Value = "PRINTING" Or _
Sheet7.Cells(x, "AL").Value = Val("PRINTING") Then
If Sheet7.Cells(x, "AM").Value = "PENDING" Or _
Sheet7.Cells(x, "AM").Value = Val("PENDING") Then
With Me.ListBox2
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 25) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 25) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
Next
On Error Resume Next
Dim a As Integer
Dim b As Integer
Dim mylist As Variant
With Me.ListBox1
For a = 1 To .ListCount - 1
For b = 1 To .ListCount - 1
If CDate(.List(b, 5)) > CDate(.List(a, 5)) Then
For c = 0 To 6
mylist = .List(b, c)
.List(b, c) = .List(a, c)
.List(a, c) = mylist
Next c
End If
Next b
Next a
End With
End If
'---
If ComboBox1.Value = "SLOTTING" Then
'slotting
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "SLOTTING" Or _
Sheet7.Cells(x, "AL").Value = Val("SLOTTING") Then
If Sheet7.Cells(x, "AM").Value = "LINED-UP" Or _
Sheet7.Cells(x, "AM").Value = Val("LINED-UP") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
If Sheet7.Cells(x, "AL").Value = "SLOTTING" Or _
Sheet7.Cells(x, "AL").Value = Val("SLOTTING") Then
If Sheet7.Cells(x, "AM").Value = "PENDING" Or _
Sheet7.Cells(x, "AM").Value = Val("PENDING") Then
With Me.ListBox2
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 10) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 10) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
Next
On Error Resume Next
Dim f As Integer
Dim g As Integer
Dim polist As Variant
With Me.ListBox1
For f = 1 To .ListCount - 1
For g = 1 To .ListCount - 1
If CDate(.List(g, 5)) > CDate(.List(f, 5)) Then
For c = 0 To 6
polist = .List(g, c)
.List(g, c) = .List(f, c)
.List(f, c) = polist
Next c
End If
Next g
Next f
End With
End If
'---
If ComboBox1.Value = "DIECUTTING" Then
'diecutting
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "DIECUTTING" Or _
Sheet7.Cells(x, "AL").Value = Val("DIECUTTING") Then
If Sheet7.Cells(x, "AM").Value = "LINED-UP" Or _
Sheet7.Cells(x, "AM").Value = Val("LINED-UP") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
If Sheet7.Cells(x, "AL").Value = "DIECUTTING" Or _
Sheet7.Cells(x, "AL").Value = Val("DIECUTTING") Then
If Sheet7.Cells(x, "AM").Value = "PENDING" Or _
Sheet7.Cells(x, "AM").Value = Val("PENDING") Then
With Me.ListBox2
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum / 2) / 16) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum / 2) / 16) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
Next
'---
On Error Resume Next
Dim h As Integer
Dim i As Integer
Dim walist As Variant
With Me.ListBox1
For h = 1 To .ListCount - 1
For i = 1 To .ListCount - 1
If CDate(.List(i, 5)) > CDate(.List(h, 5)) Then
For c = 0 To 6
walist = .List(i, c)
.List(i, c) = .List(h, c)
.List(h, c) = walist
Next c
End If
Next i
Next h
End With
End If
'---
If ComboBox1.Value = "FINISHING" Then
'finishing
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "FINISHING" Or _
Sheet7.Cells(x, "AL").Value = Val("FINISHING") Then
If Sheet7.Cells(x, "AM").Value = "LINED-UP" Or _
Sheet7.Cells(x, "AM").Value = Val("LINED-UP") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
If Sheet7.Cells(x, "AL").Value = "FINISHING" Or _
Sheet7.Cells(x, "AL").Value = Val("FINISHING") Then
If Sheet7.Cells(x, "AM").Value = "PENDING" Or _
Sheet7.Cells(x, "AM").Value = Val("PENDING") Then
With Me.ListBox2
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 190) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 190) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
Next
On Error Resume Next
Dim j As Integer
Dim k As Integer
Dim exlist As Variant
With Me.ListBox1
For j = 1 To .ListCount - 1
For k = 1 To .ListCount - 1
If CDate(.List(k, 5)) > CDate(.List(j, 5)) Then
For c = 0 To 6
exlist = .List(k, c)
.List(k, c) = .List(j, c)
.List(j, c) = exlist
Next c
End If
Next k
Next j
End With
End If
'---
If ComboBox1.Value = "For Evaluation" Then
'for evaluation
lastrow = Sheets("Status").Range("AL" & Rows.Count).End(xlUp).Row
For x = 2 To lastrow
If Sheet7.Cells(x, "AL").Value = "For Evaluation" Or _
Sheet7.Cells(x, "AL").Value = Val("For Evaluation") Then
With Me.ListBox1
.additem Sheet7.Cells(x, "A").Value
.List(.ListCount - 1, 1) = Sheet7.Cells(x, "B").Value
.List(.ListCount - 1, 2) = Sheet7.Cells(x, "C").Value
.List(.ListCount - 1, 3) = Sheet7.Cells(x, "D").Value
.List(.ListCount - 1, 4) = Sheet7.Cells(x, "E").Value
.List(.ListCount - 1, 5) = Format(Sheet7.Cells(x, "F").Value, "MMM DD, YYYY")
.List(.ListCount - 1, 6) = Sheet7.Cells(x, "G").Value
End With
End If
mysum = 0
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 190) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
Next
End If
End Sub
Private Sub CommandButton7_Click()
Sheets("Status").Visible = xlVeryHidden
Unload Me
End Sub
Private Sub ListBox1_Click()
MsgBox Me.ListBox1.ListIndex + 1
End Sub
Private Sub UserForm_Initialize()
Dim x As Integer
For x = 40 To 45
Me.ComboBox1.additem Sheet7.Cells(1, x).Value
Next
With Me.ListBox1
.ColumnCount = 7
.ColumnWidths = "40;40;150;60;250;80;40"
End With
With Me.ListBox2
.ColumnCount = 7
.ColumnWidths = "40;40;150;60;250;80;40"
End With
End Sub
Private Sub UserForm_QueryClose(cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
cancel = True
End If
End Sub
'abcdefghijklmnopqrstuvwxyz1234567890
Private Sub ListBox1_BeforeDragOver _
(ByVal cancel As MSForms.ReturnBoolean, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, ByVal y As Single, _
ByVal DragState As Long, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
cancel = True
Effect = 1
End Sub
Private Sub ListBox2_BeforeDragOver _
(ByVal cancel As MSForms.ReturnBoolean, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, ByVal y As Single, _
ByVal DragState As Long, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
cancel = True
Effect = 1
End Sub
Private Sub ListBox1_BeforeDropOrPaste _
(ByVal cancel As MSForms.ReturnBoolean, _
ByVal Action As Long, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, _
ByVal y As Single, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
On Error Resume Next
TextBox1.SetFocus
TextBox2.SetFocus
TextBox3.SetFocus
TextBox4.SetFocus
cancel = True
Effect = 1
With ListBox1
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) Then
.additem ListBox2.List(i, 0)
.List(.ListCount - 1, 1) = ListBox2.List(i, 1)
.List(.ListCount - 1, 2) = ListBox2.List(i, 2)
.List(.ListCount - 1, 3) = ListBox2.List(i, 3)
.List(.ListCount - 1, 4) = ListBox2.List(i, 4)
.List(.ListCount - 1, 5) = ListBox2.List(i, 5)
.List(.ListCount - 1, 6) = ListBox2.List(i, 6)
ListBox2.RemoveItem (i)
End If
Next
End With
If ListBox2.Selected(i) Then
TextBox5.Value = ListBox2.ListIndex(i, 1)
End If
mysum = 0
If ComboBox1.Value = "CREASING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 22) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 22) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "PRINTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 25) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 25) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "SLOTTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 10) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 10) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "DIECUTTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum / 2) / 16) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum / 2) / 16) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "FINISHING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 190) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 190) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
End Sub
Private Sub ListBox2_BeforeDropOrPaste _
(ByVal cancel As MSForms.ReturnBoolean, _
ByVal Action As Long, _
ByVal Data As MSForms.DataObject, _
ByVal x As Single, _
ByVal y As Single, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)
On Error Resume Next
TextBox1.SetFocus
TextBox2.SetFocus
TextBox3.SetFocus
TextBox4.SetFocus
cancel = True
Effect = 1
With ListBox2
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
.additem ListBox1.List(i, 0)
.List(.ListCount - 1, 1) = ListBox1.List(i, 1)
.List(.ListCount - 1, 2) = ListBox1.List(i, 2)
.List(.ListCount - 1, 3) = ListBox1.List(i, 3)
.List(.ListCount - 1, 4) = ListBox1.List(i, 4)
.List(.ListCount - 1, 5) = ListBox1.List(i, 5)
.List(.ListCount - 1, 6) = ListBox1.List(i, 6)
ListBox1.RemoveItem (i)
End If
Next
End With
mysum = 0
If ComboBox1.Value = "CREASING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 22) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 22) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "PRINTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 25) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 25) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "SLOTTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum * 2) / 10) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum * 2) / 10) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "DIECUTTING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format(((mysum / 2) / 16) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format(((mysum / 2) / 16) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
If ComboBox1.Value = "FINISHING" Then
With ListBox1
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox1 = Format((mysum / 190) + ((Me.ListBox1.ListCount - 1) * 30), "#,##0")
Me.TextBox2 = Me.ListBox1.ListCount - 1
With ListBox2
For R = 0 To .ListCount - 1
mysum = mysum + .List(R, 6)
Next R
End With
Me.TextBox3 = Format((mysum / 190) + ((Me.ListBox2.ListCount - 1) * 30), "#,##0")
Me.TextBox4 = Me.ListBox2.ListCount - 1
End If
End Sub
Private Sub ListBox1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, _
ByVal y As Single)
Dim MyDataObject As DataObject
If Button = 1 Then
Set MyDataObject = New DataObject
Dim Effect As Integer
MyDataObject.SetText ListBox1.Value
Effect = MyDataObject.StartDrag
End If
End Sub
Private Sub ListBox2_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, _
ByVal y As Single)
Dim MyDataObject As DataObject
If Button = 1 Then
Set MyDataObject = New DataObject
Dim Effect As Integer
MyDataObject.SetText ListBox2.Value
Effect = MyDataObject.StartDrag
End If
End Sub
Last edited by a moderator: