drag and drop listbox that updates at worksheet

M

monvarona

New Member
Joined
Mar 22, 2023
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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;

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:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
279
campbell1093
C
I
  • Question Question
Select Listbox value to be then taken to customer on worksheet
Replies
1
Views
138
ipbr21054
I
I
  • Solved
Also have the row number shown in Listbox on userform advice
Replies
7
Views
147
NoSparks
N
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
G
  • Question Question
Move the selected item up one position in the list Userform ListBox
Replies
4
Views
93
giri u
G

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top