Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub cmdAdd_Click()
- If TrialVersion Then Exit Sub
- Dim irow As Long
- Dim lastRow As Long
- Dim iCol As String
- Dim c As Range
- Dim ws As Worksheet
- Dim value As Long
- Dim NewPart As Boolean
- Dim ws_warehouse(7) As Worksheet '7 is total warehouse tab you have
- Set ws = Worksheets("Main")
- Set ws_warehouse(1) = Worksheets("Elkhart East")
- Set ws_warehouse(2) = Worksheets("Tennessee")
- Set ws_warehouse(3) = Worksheets("Alabama")
- Set ws_warehouse(4) = Worksheets("North Carolina")
- Set ws_warehouse(5) = Worksheets("Pennsylvania")
- Set ws_warehouse(6) = Worksheets("Texas")
- Set ws_warehouse(7) = Worksheets("West Coast")
- Set c = ws.Range("A7:A1048576").Find(What:=Me.PartTextBox.value, SearchOrder:=xlRows, _
- SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole)
- If c Is Nothing Then
- 'find first empty row in database
- lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
- irow = lastRow + 1
- NewPart = True
- Else
- 'find row where the part is
- irow = ws.Cells.Find(What:=Me.PartTextBox.value, SearchOrder:=xlRows, _
- SearchDirection:=xlPrevious, LookIn:=xlValues).Row
- NewPart = False
- End If
- 'check for a part number
- If Trim(Me.PartTextBox.value) = "" Then
- Me.PartTextBox.SetFocus
- MsgBox "Please Enter A Part Number"
- Exit Sub
- End If
- If Trim(Me.MonthComboBox.value) = "" Then
- Me.MonthComboBox.SetFocus
- MsgBox "Please Enter A Month"
- Exit Sub
- End If
- If Trim(Me.AddTextBox.value) = "" Then
- Me.AddTextBox.SetFocus
- MsgBox "Please Enter A Value To Add Or Substract"
- Exit Sub
- End If
- Select Case MonthComboBox.value
- Case "Current Month"
- iCol = "C"
- Case "Current Month +1"
- iCol = "N"
- Case "Current Month +2"
- iCol = "O"
- Case "Current Month +3"
- iCol = "P"
- Case "Current Month +4"
- iCol = "Q"
- End Select
- actWarehouse = Me.warehousecombobox.ListIndex + 1
- With ws
- .Cells(irow, "A").value = Me.PartTextBox.value
- .Cells(irow, iCol).value = .Cells(irow, iCol).value + CLng(Me.AddTextBox.value)
- End With
- With ws_warehouse(actWarehouse)
- 'find part number
- l_row = .Range("A" & .Rows.Count).End(xlUp).Row
- NewPart = True
- For r = 7 To l_row
- If Trim(.Range("A" & r)) = "" Then Exit For
- If LCase(.Range("A" & r)) = LCase(Me.PartTextBox.Text) Then
- irow = r
- Exit For
- NewPart = False
- End If
- Next r
- If NewPart Then irow = r
- .Cells(irow, "A").value = Me.PartTextBox.value
- .Cells(irow, iCol).value = .Cells(irow, iCol).value + CLng(Me.AddTextBox.value)
- End With
- 'clear the data
- Me.PartTextBox.value = ""
- Me.MonthComboBox.value = ""
- Me.AddTextBox.value = ""
- Me.PartTextBox.SetFocus
- Me.warehousecombobox.value = ""
- End Sub
- Private Sub cmdClose_Click()
- Unload Me
- End Sub
- Private Sub UserForm_Initialize()
- 'Empty NameTextBox
- PartTextBox.value = ""
- 'Empty PhoneTextBox
- AddTextBox.value = ""
- 'Empty DinnerComboBox
- 'Fill DinnerComboBox
- With MonthComboBox
- .AddItem "Current Month"
- .AddItem "Current Month +1"
- .AddItem "Current Month +2"
- .AddItem "Current Month +3"
- .AddItem "Current Month +4"
- End With
- With warehousecombobox
- .AddItem "Elkhart East"
- .AddItem "Tennessee"
- .AddItem "Alabama"
- .AddItem "North Carolina"
- .AddItem "Pennsylvania"
- .AddItem "Texas"
- .AddItem "West Coast"
- End With
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement