I'm a bit stuck with this part.
I have created a userform with different sections. First Section will ask for Part# the userform will search in column A if the part exists if it doesn't it will add it to the last row available. Second section is a combobox selecting a month to add a value to (Columns C,N,O,P,Q). Next Section is the value amount given to the part based on which month is selected. And the next section is where I'm stuck in the warehouse section I want to select a warehouse this references the tabs I created "Elkhart", "Tennessee" etc. I want to be able to add the part in the active sheet which is the "main" and also based on warehouse combobox option to add it to the last row of the tab.
Ex. Part:Bob Month:Current Add:200 Warehouse:Elkhart East
Private Sub cmdAdd_Click()
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
Set ws = Worksheets("Main")
Set wsE = Worksheets("Elkhart East")
Set wsT = Worksheets("Tennessee")
Set wsA = Worksheets("Alabama")
Set wsN = Worksheets("North Carolina")
Set wsP = Worksheets("Pennsylvania")
Set wsT = Worksheets("Texas")
Set wsW = 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
[B]**Select Case warehousecombobox.value
Case "Elkhart East"
iCol = wsE And irow
Case "Tennessee"
iCol = wsT And irow
Case "Alabama"
iCol = wsA And irow
Case "North Carolina"
iCol = wsN And irow
Case "Pennsylvania"
iCol = wsP And irow
Case "Texas"
iCol = wsT And irow
Case "West Coast"
iCol = wsW And irow
End Select[/B]
value = Cells(irow, iCol).value**
With ws
.Cells(irow, iCol).value = value + CLng(Me.AddTextBox.value)
End With
If NewPart = True Then
ws.Cells(irow, "A").value = Me.PartTextBox.value
End If
If NewPart = True Then
ws.Cells(irow, "C").value = Me.AddTextBox.value
End If
'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 MonthComboBox_Change()
End Sub
Private Sub Warehousecombobox_Change()
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
Display More