Can someone show me what code I would need to add to my current code. I want the "checkbox1" To look at the line that am on at on Column 'I" or "9" however that works and if the value is greater than "0" t recheck the box when the user form is opened. I am pretty new at this and have tried multiple things with no luck. Also the code I have may be interfering with it. So what I need is what Code to enter and where, and if I need to remove and of my current code. Thanks for taking a look. Current code is attached.
Code:
Option Explicit
Dim ws As Worksheet
Private Sub Cancel_Click()
Unload Me
End Sub
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
MsgBox ("Purchased Component - No Operations Permitted.")
' Clear Combo_Task Boxes contents
Combo_Task1.Clear
Combo_Task2.Clear
Combo_Task3.Clear
Combo_Task4.Clear
Combo_Task5.Clear
Combo_Task6.Clear
Combo_Task7.Clear
Combo_Task8.Clear
Combo_Task9.Clear
Combo_Task10.Clear
Combo_Task11.Clear
Combo_Task12.Clear
Combo_Task13.Clear
Combo_Task14.Clear
Combo_Task15.Clear
'Reset Combo_Task Boxes to "-"
Combo_Task1.Value = "-"
Combo_Task2.Value = "-"
Combo_Task3.Value = "-"
Combo_Task4.Value = "-"
Combo_Task5.Value = "-"
Combo_Task6.Value = "-"
Combo_Task7.Value = "-"
Combo_Task8.Value = "-"
Combo_Task9.Value = "-"
Combo_Task10.Value = "-"
Combo_Task11.Value = "-"
Combo_Task12.Value = "-"
Combo_Task13.Value = "-"
Combo_Task14.Value = "-"
Combo_Task15.Value = "-"
'Load Comboboxes with Nul Data
'Dim AddNulItemData
'AddNulItemData = Sheets("Processes").Range("A6").Value
'Me.Combo_Task1.List = AddNulItemData
'Me.Combo_Task2.List = AddNulItemData
'Me.Combo_Task3.List = AddNulItemData
'Me.Combo_Task4.List = AddNulItemData
'Me.Combo_Task5.List = AddNulItemData
'Me.Combo_Task6.List = AddNulItemData
'Me.Combo_Task7.List = AddNulItemData
'Me.Combo_Task8.List = AddNulItemData
'Me.Combo_Task9.List = AddNulItemData
'Me.Combo_Task10.List = AddNulItemData
'Me.Combo_Task11.List = AddNulItemData
'Me.Combo_Task12.List = AddNulItemData
'Me.Combo_Task13.List = AddNulItemData
'Me.Combo_Task14.List = AddNulItemData
'Me.Combo_Task15.List = AddNulItemData
Else
'Reset Combo_Task Boxes to "-"
Combo_Task1.Value = "-"
Combo_Task2.Value = "-"
Combo_Task3.Value = "-"
Combo_Task4.Value = "-"
Combo_Task5.Value = "-"
Combo_Task6.Value = "-"
Combo_Task7.Value = "-"
Combo_Task8.Value = "-"
Combo_Task9.Value = "-"
Combo_Task10.Value = "-"
Combo_Task11.Value = "-"
Combo_Task12.Value = "-"
Combo_Task13.Value = "-"
Combo_Task14.Value = "-"
Combo_Task15.Value = "-"
'Load Comboboxes with Data
Dim AddItemData
AddItemData = Sheets("Processes").Range("A6:A48").Value
Me.Combo_Task1.List = AddItemData
Me.Combo_Task2.List = AddItemData
Me.Combo_Task3.List = AddItemData
Me.Combo_Task4.List = AddItemData
Me.Combo_Task5.List = AddItemData
Me.Combo_Task6.List = AddItemData
Me.Combo_Task7.List = AddItemData
Me.Combo_Task8.List = AddItemData
Me.Combo_Task9.List = AddItemData
Me.Combo_Task10.List = AddItemData
Me.Combo_Task11.List = AddItemData
Me.Combo_Task12.List = AddItemData
Me.Combo_Task13.List = AddItemData
Me.Combo_Task14.List = AddItemData
Me.Combo_Task15.List = AddItemData
End If
End Sub
Private Sub ClearForm_Click()
Call UserForm_Initialize
CheckBox1.Value = True
End Sub
Private Sub CommandButton1_Click()
SearchMaterial3.Show
End Sub
Private Sub CommandButton2_Click()
SearchMaterial4.Show
End Sub
Private Sub CreateSubCom_Click()
'No code to add Part Number to worksheet Complete_Assy as it already appears. Data will change based on formulas.
Dim PartNumRow As String
'Make SubCom2 Active
Sheets("StartHereSubCom").Activate
'Determine PartNumRow
'PartNumRow = Application.WorksheetFunction.Match(In_SubComPNmod2.Value, Worksheets("StartHereSubCom").Range("A1:A999"), 0)
PartNumRow = Application.WorksheetFunction.Match(In_SubComPNmod.Caption, Worksheets("StartHereSubCom").Range("A1:A999"), 0)
'MsgBox "Found Original PN at row : " & PartNumRow
'Export Data to worksheet
'Cells(PartNumRow, 1).Value = In_SubComPNmod.Caption
'Cells(PartNumRow, 2).Value =
'Cells(PartNumRow, 3).Value =
'Cells(PartNumRow, 4).Value =
Cells(PartNumRow, 6).Value = In_SubComDescribe.Value
Cells(PartNumRow, 15).Value = In_Material2.Value
Cells(PartNumRow, 16).Value = In_Quantity2.Value
Cells(PartNumRow, 20).Value = In_Units2.Value
Cells(PartNumRow, 17).Value = In_Material.Value
Cells(PartNumRow, 18).Value = In_Quantity.Value
Cells(PartNumRow, 19).Value = In_Units.Value
Cells(PartNumRow, 21).Value = In_CostPerUnit.Value
Cells(PartNumRow, 23).Value = In_CostPerUnit2.Value
Cells(PartNumRow, 26).Value = Combo_Task1.Value
Cells(PartNumRow, 27).Value = In_Time1.Value
Cells(PartNumRow, 30).Value = In_SetupFee1.Value
'Cells(PartNumRow, 31).Value = In_SetupApplied1.Value
Cells(PartNumRow, 32).Value = In_Tooling1.Value
Cells(PartNumRow, 33).Value = In_Notes1.Value
Cells(PartNumRow, 34).Value = Combo_Task2.Value
Cells(PartNumRow, 35).Value = In_Time2.Value
Cells(PartNumRow, 38).Value = In_SetupFee2.Value
'Cells(PartNumRow, 39).Value = In_SetupApplied2.Value
Cells(PartNumRow, 40).Value = In_Tooling2.Value
Cells(PartNumRow, 41).Value = In_Notes2.Value
Cells(PartNumRow, 42).Value = Combo_Task3.Value
Cells(PartNumRow, 43).Value = In_Time3.Value
Cells(PartNumRow, 46).Value = In_SetupFee3.Value
'Cells(PartNumRow, 47).Value = In_SetupApplied3.Value
Cells(PartNumRow, 48).Value = In_Tooling3.Value
Cells(PartNumRow, 49).Value = In_Notes3.Value
Cells(PartNumRow, 50).Value = Combo_Task4.Value
Cells(PartNumRow, 51).Value = In_Time4.Value
Cells(PartNumRow, 54).Value = In_SetupFee4.Value
'Cells(PartNumRow, 55).Value = In_SetupApplied4.Value
Cells(PartNumRow, 56).Value = In_Tooling4.Value
Cells(PartNumRow, 57).Value = In_Notes4.Value
Cells(PartNumRow, 58).Value = Combo_Task5.Value
Cells(PartNumRow, 59).Value = In_Time5.Value
Cells(PartNumRow, 62).Value = In_SetupFee5.Value
'Cells(PartNumRow, 63).Value = In_SetupApplied5.Value
Cells(PartNumRow, 64).Value = In_Tooling5.Value
Cells(PartNumRow, 65).Value = In_Notes5.Value
Cells(PartNumRow, 66).Value = Combo_Task6.Value
Cells(PartNumRow, 67).Value = In_Time6.Value
Cells(PartNumRow, 70).Value = In_SetupFee6.Value
'Cells(PartNumRow, 71).Value = In_SetupApplied6.Value
Cells(PartNumRow, 72).Value = In_Tooling6.Value
Cells(PartNumRow, 73).Value = In_Notes6.Value
Cells(PartNumRow, 74).Value = Combo_Task7.Value
Cells(PartNumRow, 75).Value = In_Time7.Value
Cells(PartNumRow, 78).Value = In_SetupFee7.Value
'Cells(PartNumRow, 79).Value = In_SetupApplied7.Value
Cells(PartNumRow, 80).Value = In_Tooling7.Value
Cells(PartNumRow, 81).Value = In_Notes7.Value
Cells(PartNumRow, 82).Value = Combo_Task8.Value
Cells(PartNumRow, 83).Value = In_Time8.Value
Cells(PartNumRow, 86).Value = In_SetupFee8.Value
'Cells(PartNumRow, 87).Value = In_SetupApplied8.Value
Cells(PartNumRow, 88).Value = In_Tooling8.Value
Cells(PartNumRow, 89).Value = In_Notes8.Value
Cells(PartNumRow, 90).Value = Combo_Task9.Value
Cells(PartNumRow, 91).Value = In_Time9.Value
Cells(PartNumRow, 94).Value = In_SetupFee9.Value
'Cells(PartNumRow, 95).Value = In_SetupApplied9.Value
Cells(PartNumRow, 96).Value = In_Tooling9.Value
Cells(PartNumRow, 97).Value = In_Notes9.Value
Cells(PartNumRow, 98).Value = Combo_Task10.Value
Cells(PartNumRow, 99).Value = In_Time10.Value
Cells(PartNumRow, 102).Value = In_SetupFee10.Value
'Cells(PartNumRow, 103).Value = In_SetupApplied10.Value
Cells(PartNumRow, 104).Value = In_Tooling10.Value
Cells(PartNumRow, 105).Value = In_Notes10.Value
Cells(PartNumRow, 106).Value = Combo_Task11.Value
Cells(PartNumRow, 107).Value = In_Time11.Value
Cells(PartNumRow, 110).Value = In_SetupFee11.Value
'Cells(PartNumRow, 111).Value = In_SetupApplied11.Value
Cells(PartNumRow, 112).Value = In_Tooling11.Value
Cells(PartNumRow, 113).Value = In_Notes11.Value
Cells(PartNumRow, 114).Value = Combo_Task12.Value
Cells(PartNumRow, 115).Value = In_Time12.Value
Cells(PartNumRow, 118).Value = In_SetupFee12.Value
'Cells(PartNumRow, 119).Value = In_SetupApplied12.Value
Cells(PartNumRow, 120).Value = In_Tooling12.Value
Cells(PartNumRow, 121).Value = In_Notes12.Value
Cells(PartNumRow, 122).Value = Combo_Task13.Value
Cells(PartNumRow, 123).Value = In_Time13.Value
Cells(PartNumRow, 126).Value = In_SetupFee13.Value
'Cells(PartNumRow, 127).Value = In_SetupApplied13.Value
Cells(PartNumRow, 128).Value = In_Tooling13.Value
Cells(PartNumRow, 129).Value = In_Notes13.Value
Cells(PartNumRow, 130).Value = Combo_Task14.Value
Cells(PartNumRow, 131).Value = In_Time14.Value
Cells(PartNumRow, 134).Value = In_SetupFee14.Value
'Cells(PartNumRow, 135).Value = In_SetupApplied14.Value
Cells(PartNumRow, 136).Value = In_Tooling14.Value
Cells(PartNumRow, 137).Value = In_Notes14.Value
Cells(PartNumRow, 138).Value = Combo_Task15.Value
Cells(PartNumRow, 139).Value = In_Time15.Value
Cells(PartNumRow, 142).Value = In_SetupFee15.Value
'Cells(PartNumRow, 143).Value = In_SetupApplied15.Value
Cells(PartNumRow, 144).Value = In_Tooling15.Value
Cells(PartNumRow, 145).Value = In_Notes15.Value
'Calculated Data Export to worksheet
'Material Information
'Cost per Part
If Cells(PartNumRow, 18).Value > 0 Then
Cells(PartNumRow, 22).Value = In_CostPerUnit.Value * Cells(PartNumRow, 18).Value
Else
Cells(PartNumRow, 22).Value = 0
End If
'Cost per Part 2
If Cells(PartNumRow, 16).Value > 0 Then
Cells(PartNumRow, 24).Value = In_CostPerUnit2.Value * Cells(PartNumRow, 16).Value
Else
Cells(PartNumRow, 24).Value = 0
End If
'Cost per Part Total
Cells(PartNumRow, 7).Value = Cells(PartNumRow, 24).Value + Cells(PartNumRow, 22).Value
'Task1 Information
'Labor Rate1
Cells(PartNumRow, 28).Value = Application.WorksheetFunction.VLookup(Combo_Task1.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge1
Cells(PartNumRow, 29).Value = (Cells(PartNumRow, 28).Value / 60) * Cells(PartNumRow, 27).Value
'Task2 Information
'Labor Rate2
Cells(PartNumRow, 36).Value = Application.WorksheetFunction.VLookup(Combo_Task2.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge2 Labor Rate2 Time2
Cells(PartNumRow, 37).Value = (Cells(PartNumRow, 36).Value / 60) * Cells(PartNumRow, 35).Value
'Task3 Information
'Labor Rate3
Cells(PartNumRow, 44).Value = Application.WorksheetFunction.VLookup(Combo_Task3.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge3 Labor Rate3 Time3
Cells(PartNumRow, 45).Value = (Cells(PartNumRow, 44).Value / 60) * Cells(PartNumRow, 43).Value
'Task4 Information
'Labor Rate4
Cells(PartNumRow, 52).Value = Application.WorksheetFunction.VLookup(Combo_Task4.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge4 Labor Rate4 Time4
Cells(PartNumRow, 53).Value = (Cells(PartNumRow, 52).Value / 60) * Cells(PartNumRow, 51).Value
'Task5 Information
'Labor Rate5
Cells(PartNumRow, 60).Value = Application.WorksheetFunction.VLookup(Combo_Task5.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge5 Labor Rate5 Time5
Cells(PartNumRow, 61).Value = (Cells(PartNumRow, 60).Value / 60) * Cells(PartNumRow, 59).Value
'Task6 Information
'Labor Rate6
Cells(PartNumRow, 68).Value = Application.WorksheetFunction.VLookup(Combo_Task6.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge6 Labor Rate6 Time6
Cells(PartNumRow, 69).Value = (Cells(PartNumRow, 68).Value / 60) * Cells(PartNumRow, 67).Value
'Task7 Information
'Labor Rate7
Cells(PartNumRow, 76).Value = Application.WorksheetFunction.VLookup(Combo_Task7.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge7 Labor Rate7 Time7
Cells(PartNumRow, 77).Value = (Cells(PartNumRow, 76).Value / 60) * Cells(PartNumRow, 75).Value
'Task8 Information
'Labor Rate8
Cells(PartNumRow, 84).Value = Application.WorksheetFunction.VLookup(Combo_Task8.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge8 Labor Rate8 Time8
Cells(PartNumRow, 85).Value = (Cells(PartNumRow, 84).Value / 60) * Cells(PartNumRow, 83).Value
'Task9 Information
'Labor Rate9
Cells(PartNumRow, 92).Value = Application.WorksheetFunction.VLookup(Combo_Task9.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge9 Labor Rate9 Time9
Cells(PartNumRow, 93).Value = (Cells(PartNumRow, 92).Value / 60) * Cells(PartNumRow, 91).Value
'Task10 Information
'Labor Rate10
Cells(PartNumRow, 100).Value = Application.WorksheetFunction.VLookup(Combo_Task10.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge10 Labor Rate10 Time10
Cells(PartNumRow, 101).Value = (Cells(PartNumRow, 100).Value / 60) * Cells(PartNumRow, 99).Value
'Task11 Information
'Labor Rate11
Cells(PartNumRow, 108).Value = Application.WorksheetFunction.VLookup(Combo_Task11.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge11 Labor Rate11 Time11
Cells(PartNumRow, 109).Value = (Cells(PartNumRow, 108).Value / 60) * Cells(PartNumRow, 107).Value
'Task12 Information
'Labor Rate12
Cells(PartNumRow, 116).Value = Application.WorksheetFunction.VLookup(Combo_Task12.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge12 Labor Rate12 Time12
Cells(PartNumRow, 117).Value = (Cells(PartNumRow, 116).Value / 60) * Cells(PartNumRow, 115).Value
'Task13 Information
'Labor Rate13
Cells(PartNumRow, 124).Value = Application.WorksheetFunction.VLookup(Combo_Task13.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge13 Labor Rate13 Time13
Cells(PartNumRow, 125).Value = (Cells(PartNumRow, 124).Value / 60) * Cells(PartNumRow, 123).Value
'Task14 Information
'Labor Rate14
Cells(PartNumRow, 132).Value = Application.WorksheetFunction.VLookup(Combo_Task14.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge14 Labor Rate14 Time14
Cells(PartNumRow, 133).Value = (Cells(PartNumRow, 132).Value / 60) * Cells(PartNumRow, 131).Value
'Task15 Information
'Labor Rate15
Cells(PartNumRow, 140).Value = Application.WorksheetFunction.VLookup(Combo_Task15.Value, Range("Processes!$A$6:$N$48"), 11, False)
'Charge15 Labor Rate15 Time15
Cells(PartNumRow, 141).Value = (Cells(PartNumRow, 140).Value / 60) * Cells(PartNumRow, 139).Value
'Polaris Data
'Raw Material
Cells(PartNumRow, 8).Value = Cells(PartNumRow, 7).Value
'Direct Labor
'Cells to add for Charge Task1 Task 2 Task3 Task4 Task5 Task6 Task7 Task8 Task9 Task10 Task11 Task12 Task13 Task14 Task15
Cells(PartNumRow, 10).Value = Cells(PartNumRow, 29).Value + Cells(PartNumRow, 37).Value + Cells(PartNumRow, 45).Value + Cells(PartNumRow, 53).Value + Cells(PartNumRow, 61).Value + Cells(PartNumRow, 69).Value + Cells(PartNumRow, 77).Value + Cells(PartNumRow, 85).Value + Cells(PartNumRow, 93).Value + Cells(PartNumRow, 101).Value + Cells(PartNumRow, 109).Value + Cells(PartNumRow, 117).Value + Cells(PartNumRow, 125).Value + Cells(PartNumRow, 133).Value + Cells(PartNumRow, 141).Value
'Setup Fee Total Setup Fee 1 Setup Fee 2 Setup Fee3 Setup Fee4 Setup Fee5 Setup Fee6 Setup Fee7 Setup Fee8 Setup Fee9 Setup Fee10 Setup Fee11 Setup Fee12 Setup Fee13 Setup Fee14 Setup Fee15
Cells(PartNumRow, 11).Value = Cells(PartNumRow, 30).Value + Cells(PartNumRow, 38).Value + Cells(PartNumRow, 46).Value + Cells(PartNumRow, 54).Value + Cells(PartNumRow, 62).Value + Cells(PartNumRow, 70).Value + Cells(PartNumRow, 78).Value + Cells(PartNumRow, 86).Value + Cells(PartNumRow, 94).Value + Cells(PartNumRow, 102).Value + Cells(PartNumRow, 110).Value + Cells(PartNumRow, 118).Value + Cells(PartNumRow, 126).Value + Cells(PartNumRow, 134).Value + Cells(PartNumRow, 142).Value
'Tooling Total Tooling1 Tooling2 Tooling3 Tooling4 Tooling5 Tooling6 Tooling7 Tooling8 Tooling9 Tooling10 Tooling11 Tooling12 Tooling13 Tooling14 Tooling15
Cells(PartNumRow, 12).Value = Cells(PartNumRow, 32).Value + Cells(PartNumRow, 40).Value + Cells(PartNumRow, 48).Value + Cells(PartNumRow, 56).Value + Cells(PartNumRow, 64).Value + Cells(PartNumRow, 72).Value + Cells(PartNumRow, 80).Value + Cells(PartNumRow, 88).Value + Cells(PartNumRow, 96).Value + Cells(PartNumRow, 104).Value + Cells(PartNumRow, 112).Value + Cells(PartNumRow, 120).Value + Cells(PartNumRow, 128).Value + Cells(PartNumRow, 136).Value + Cells(PartNumRow, 144).Value
'Setup Applied Fee Total Applied1 Applied2 Applied3 Applied4 Applied5 Applied6 Applied7 Applied8 Applied9 Applied10 Applied11 Applied12 Applied13 Applied14 Applied15
Cells(PartNumRow, 13).Value = Cells(PartNumRow, 31).Value + Cells(PartNumRow, 39).Value + Cells(PartNumRow, 47).Value + Cells(PartNumRow, 55).Value + Cells(PartNumRow, 64).Value + Cells(PartNumRow, 71).Value + Cells(PartNumRow, 79).Value + Cells(PartNumRow, 87).Value + Cells(PartNumRow, 95).Value + Cells(PartNumRow, 103).Value + Cells(PartNumRow, 111).Value + Cells(PartNumRow, 119).Value + Cells(PartNumRow, 127).Value + Cells(PartNumRow, 135).Value + Cells(PartNumRow, 143).Value
'Polaris Price per Part Verification
Cells(PartNumRow, 14).Value = Cells(PartNumRow, 8).Value + Cells(PartNumRow, 9).Value + Cells(PartNumRow, 10).Value
'+ Cells(PartNumRow, 11).Value + Cells(PartNumRow, 12).Value + Cells(PartNumRow, 13).Value
'Cost per Part Total Material Cost Task Cost1 Task Cost2 Task Cost3 Task Cost4 Task Cost5 Task Cost6 Task Cost7 Task Cost8 Task Cost9 Task Cost10 Task Cost11 Task Cost12 Task Cost13 Task Cost14 Task Cost15
Cells(PartNumRow, 2).Value = Cells(PartNumRow, 7).Value + Cells(PartNumRow, 29).Value + Cells(PartNumRow, 37).Value + Cells(PartNumRow, 45).Value + Cells(PartNumRow, 53).Value + Cells(PartNumRow, 61).Value + Cells(PartNumRow, 69).Value + Cells(PartNumRow, 77).Value + Cells(PartNumRow, 85).Value + Cells(PartNumRow, 93).Value + Cells(PartNumRow, 101).Value + Cells(PartNumRow, 109).Value + Cells(PartNumRow, 117).Value + Cells(PartNumRow, 125).Value + Cells(PartNumRow, 133).Value + Cells(PartNumRow, 141).Value
'Purchased Component Costs
If CheckBox1.Value = True Then
Cells(PartNumRow, 9).Value = Cells(PartNumRow, 2).Value
Else
Cells(PartNumRow, 9).Value = 0
End If
If Cells(PartNumRow, 9).Value > 0 Then
Cells(PartNumRow, 8).Value = 0
Else
Cells(PartNumRow, 8).Value = Cells(PartNumRow, 7).Value
End If
Dim r
Dim v
Dim s
Dim t
Dim w
Dim setapplyfeeA
Dim setapplyfeeB
Dim setapplyfeeC
Dim ToolfeeA
Dim ToolfeeB
Dim ToolfeeC
r = "Sub-Component Part Number - " & Cells(PartNumRow, 1).Value & vbNewLine & "Sub-Component Description - " & Cells(PartNumRow, 6).Value & vbNewLine & "Part Cost - " & Cells(PartNumRow, 2).Value & vbNewLine & "Raw Material Cost - " & Cells(PartNumRow, 8).Value & vbNewLine & "Purchased Component Cost - " & Cells(PartNumRow, 9).Value & vbNewLine & "Net Labor/Machine Cost - " & Cells(PartNumRow, 10).Value & vbNewLine & "Material Name - " & Cells(PartNumRow, 17).Value & vbNewLine & "Quantity - " & Cells(PartNumRow, 18).Value & vbNewLine & "Units - " & Cells(PartNumRow, 19).Value & vbNewLine & "Cost per Unit - " & Cells(PartNumRow, 21).Value & vbNewLine & "Cost per Part - " & Cells(PartNumRow, 22).Value
v = "Material 2 Name - " & Cells(PartNumRow, 15).Value & vbNewLine & "Quantity 2 - " & Cells(PartNumRow, 16).Value & vbNewLine & "Units 2- " & Cells(PartNumRow, 20).Value & vbNewLine & "Cost per Unit 2- " & Cells(PartNumRow, 23).Value & vbNewLine & "Cost per Part 2 - " & Cells(PartNumRow, 24).Value
s = "Task 1 - " & Cells(PartNumRow, 26).Value & vbNewLine & "Time 1 - " & Cells(PartNumRow, 27).Value & vbNewLine & "Task 2 - " & Cells(PartNumRow, 34).Value & vbNewLine & "Time 2 - " & Cells(PartNumRow, 35).Value & vbNewLine & "Task 3 - " & Cells(PartNumRow, 42).Value & vbNewLine & "Time 3 - " & Cells(PartNumRow, 43).Value & vbNewLine & "Task 4 - " & Cells(PartNumRow, 51).Value & vbNewLine & "Time 4 - " & Cells(PartNumRow, 52).Value & vbNewLine & "Task 5 - " & Cells(PartNumRow, 58).Value & vbNewLine & "Time 5 - " & Cells(PartNumRow, 59).Value
t = "Task 6 - " & Cells(PartNumRow, 66).Value & vbNewLine & "Time 6 - " & Cells(PartNumRow, 67).Value & vbNewLine & "Task 7 - " & Cells(PartNumRow, 74).Value & vbNewLine & "Time 7 - " & Cells(PartNumRow, 75).Value & vbNewLine & "Task 8 - " & Cells(PartNumRow, 82).Value & vbNewLine & "Time 8 - " & Cells(PartNumRow, 83).Value & vbNewLine & "Task 9 - " & Cells(PartNumRow, 90).Value & vbNewLine & "Time 9 - " & Cells(PartNumRow, 91).Value & vbNewLine & "Task 10 - " & Cells(PartNumRow, 98).Value & vbNewLine & "Time 10 - " & Cells(PartNumRow, 99).Value
w = "Task 11 - " & Cells(PartNumRow, 106).Value & vbNewLine & "Time 11 - " & Cells(PartNumRow, 107).Value & vbNewLine & "Task 12 - " & Cells(PartNumRow, 114).Value & vbNewLine & "Time 12 - " & Cells(PartNumRow, 115).Value & vbNewLine & "Task 13 - " & Cells(PartNumRow, 122).Value & vbNewLine & "Time 13 - " & Cells(PartNumRow, 123).Value & vbNewLine & "Task 14 - " & Cells(PartNumRow, 130).Value & vbNewLine & "Time 14 - " & Cells(PartNumRow, 131).Value & vbNewLine & "Task 15 - " & Cells(PartNumRow, 138).Value & vbNewLine & "Time 15 - " & Cells(PartNumRow, 139).Value
setapplyfeeA = "Setup Fee 1 - " & Cells(PartNumRow, 30).Value & vbNewLine & "Setup Fee 2 - " & Cells(PartNumRow, 38).Value & vbNewLine & "Setup Fee 3 - " & Cells(PartNumRow, 46).Value & vbNewLine & "Setup Fee 4 - " & Cells(PartNumRow, 54).Value & vbNewLine & "Setup Fee 5 - " & Cells(PartNumRow, 62).Value
setapplyfeeB = "Setup Fee 6 - " & Cells(PartNumRow, 70).Value & vbNewLine & "Setup Fee 7 - " & Cells(PartNumRow, 78).Value & vbNewLine & "Setup Fee 8 - " & Cells(PartNumRow, 86).Value & vbNewLine & "Setup Fee 9 - " & Cells(PartNumRow, 94).Value & vbNewLine & "Setup Fee 10 - " & Cells(PartNumRow, 102).Value
setapplyfeeC = "Setup Fee 11 - " & Cells(PartNumRow, 110).Value & vbNewLine & "Setup Fee 12 - " & Cells(PartNumRow, 118).Value & vbNewLine & "Setup Fee 13 - " & Cells(PartNumRow, 126).Value & vbNewLine & "Setup Fee 14 - " & Cells(PartNumRow, 134).Value & vbNewLine & "Setup Fee 15 - " & Cells(PartNumRow, 142).Value
ToolfeeA = "Tooling 1 - " & Cells(PartNumRow, 32).Value & vbNewLine & "Tooling 2 - " & Cells(PartNumRow, 40).Value & vbNewLine & "Tooling 3 - " & Cells(PartNumRow, 48).Value & vbNewLine & "Tooling 4 - " & Cells(PartNumRow, 56).Value & vbNewLine & "Tooling 5 - " & Cells(PartNumRow, 64).Value
ToolfeeB = "Tooling 6 - " & Cells(PartNumRow, 72).Value & vbNewLine & "Tooling 7 - " & Cells(PartNumRow, 80).Value & vbNewLine & "Tooling 8 - " & Cells(PartNumRow, 88).Value & vbNewLine & "Tooling 9 - " & Cells(PartNumRow, 96).Value & vbNewLine & "Tooling 10 - " & Cells(PartNumRow, 104).Value
ToolfeeC = "Tooling 11 - " & Cells(PartNumRow, 112).Value & vbNewLine & "Tooling 12 - " & Cells(PartNumRow, 120).Value & vbNewLine & "Tooling 13 - " & Cells(PartNumRow, 128).Value & vbNewLine & "Tooling 14 - " & Cells(PartNumRow, 136).Value & vbNewLine & "Tooling 15 - " & Cells(PartNumRow, 144).Value
'MsgBox ("New Sub-Component Created with Operations:" & vbNewLine & vbNewLine & r & vbNewLine & v)
'MsgBox ("New Sub-Component Created with Operations:" & vbNewLine & vbNewLine & s & vbNewLine & t & vbNewLine & w)
'MsgBox ("New Sub-Component Created with Setup and Applied Setup Fees:" & vbNewLine & vbNewLine & setapplyfeeA & vbNewLine & setapplyfeeB & vbNewLine & setapplyfeeC)
'MsgBox ("New Sub-Component Created with Tooling Fees:" & vbNewLine & vbNewLine & ToolfeeA & vbNewLine & ToolfeeB & vbNewLine & ToolfeeC)
'MsgBox ("Click Finished to exit.")
Unload Me
End Sub
Private Sub Combo_Task1_click()
Dim Rw1 As Long
With Me
Rw1 = Me.Combo_Task1.ListIndex + 6
.In_SetupFee1.Value = ws.Cells(Rw1, 12).Value
End With
End Sub
Private Sub Combo_Task2_click()
Dim Rw2 As Long
With Me
Rw2 = Me.Combo_Task2.ListIndex + 6
.In_SetupFee2.Value = ws.Cells(Rw2, 12).Value
End With
End Sub
Private Sub Combo_Task3_click()
Dim Rw3 As Long
With Me
Rw3 = Me.Combo_Task3.ListIndex + 6
.In_SetupFee3.Value = ws.Cells(Rw3, 12).Value
End With
End Sub
Private Sub Combo_Task4_click()
Dim Rw4 As Long
With Me
Rw4 = Me.Combo_Task4.ListIndex + 6
.In_SetupFee4.Value = ws.Cells(Rw4, 12).Value
End With
End Sub
Private Sub Combo_Task5_click()
Dim Rw5 As Long
With Me
Rw5 = Me.Combo_Task5.ListIndex + 6
.In_SetupFee5.Value = ws.Cells(Rw5, 12).Value
End With
End Sub
Private Sub Combo_Task6_click()
Dim Rw6 As Long
With Me
Rw6 = Me.Combo_Task6.ListIndex + 6
.In_SetupFee6.Value = ws.Cells(Rw6, 12).Value
End With
End Sub
Private Sub Combo_Task7_click()
Dim Rw7 As Long
With Me
Rw7 = Me.Combo_Task7.ListIndex + 6
.In_SetupFee7.Value = ws.Cells(Rw7, 12).Value
End With
End Sub
Private Sub Combo_Task8_click()
Dim Rw8 As Long
With Me
Rw8 = Me.Combo_Task8.ListIndex + 6
.In_SetupFee8.Value = ws.Cells(Rw8, 12).Value
End With
End Sub
Private Sub Combo_Task9_click()
Dim Rw9 As Long
With Me
Rw9 = Me.Combo_Task9.ListIndex + 6
.In_SetupFee9.Value = ws.Cells(Rw9, 12).Value
End With
End Sub
Private Sub Combo_Task10_click()
Dim Rw10 As Long
With Me
Rw10 = Me.Combo_Task10.ListIndex + 6
.In_SetupFee10.Value = ws.Cells(Rw10, 12).Value
End With
End Sub
Private Sub Combo_Task11_click()
Dim Rw11 As Long
With Me
Rw11 = Me.Combo_Task11.ListIndex + 6
.In_SetupFee11.Value = ws.Cells(Rw11, 12).Value
End With
End Sub
Private Sub Combo_Task12_click()
Dim Rw12 As Long
With Me
Rw12 = Me.Combo_Task12.ListIndex + 6
.In_SetupFee12.Value = ws.Cells(Rw12, 12).Value
End With
End Sub
Private Sub Combo_Task13_click()
Dim Rw13 As Long
With Me
Rw13 = Me.Combo_Task13.ListIndex + 6
.In_SetupFee13.Value = ws.Cells(Rw13, 12).Value
End With
End Sub
Private Sub Combo_Task14_click()
Dim Rw14 As Long
With Me
Rw14 = Me.Combo_Task14.ListIndex + 6
.In_SetupFee14.Value = ws.Cells(Rw14, 12).Value
End With
End Sub
Private Sub Combo_Task15_click()
Dim Rw15 As Long
With Me
Rw15 = Me.Combo_Task15.ListIndex + 6
.In_SetupFee15.Value = ws.Cells(Rw15, 12).Value
End With
End Sub
Private Sub In_Material_Change()
Dim f As Range
Set f = Sheets("Materials").Range("a:a").Find(In_Material.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
In_CostPerUnit.Value = Sheets("Materials").Cells(f.Row, "d").Value
In_Units.Value = Sheets("Materials").Cells(f.Row, "e").Value
End If
End Sub
Private Sub In_Material2_Change()
Dim f As Range
Set f = Sheets("Materials").Range("a:a").Find(In_Material2.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
In_CostPerUnit2.Value = Sheets("Materials").Cells(f.Row, "d").Value
In_Units2.Value = Sheets("Materials").Cells(f.Row, "e").Value
End If
End Sub
Private Sub UserForm_Initialize()
Set ws = Worksheets("Processes")
Unload SearchPart
'Make SearchSheet Active
Sheets("Hidden2").Activate
'Fill with PN In_SubComPN
In_SubComPNmod.Caption = Range("A12").Value
'In_SubComPNmod2.Value = Range("A12").Value
'Fill with PN In_SubComDescribe
In_SubComDescribe.Value = Range("F12").Value
'Fill with PN In_Material
In_Material.Value = Range("Q12").Value
'Fill with PN In_Quantity
In_Quantity.Value = Range("R12").Value
'Fill with PN In_Material2
In_Material2.Value = Range("O12").Value
'Fill with PN In_Quantity2
In_Quantity2.Value = Range("P12").Value
'Empty In_Notes Fill Boxes
In_Notes1.Value = Range("AG12").Value
In_Notes2.Value = Range("AO12").Value
In_Notes3.Value = Range("AW12").Value
In_Notes4.Value = Range("BE12").Value
In_Notes5.Value = Range("BM12").Value
In_Notes6.Value = Range("BU12").Value
In_Notes7.Value = Range("CC12").Value
In_Notes8.Value = Range("CK12").Value
In_Notes9.Value = Range("CS12").Value
In_Notes10.Value = Range("DA12").Value
In_Notes11.Value = Range("DI12").Value
In_Notes12.Value = Range("DQ12").Value
In_Notes13.Value = Range("DY12").Value
In_Notes14.Value = Range("EG12").Value
In_Notes15.Value = Range("EO12").Value
'Fill with PN In_SetupFees
In_SetupFee1.Value = Range("AD12").Value
In_SetupFee2.Value = Range("AL12").Value
In_SetupFee3.Value = Range("AT12").Value
In_SetupFee4.Value = Range("BB12").Value
In_SetupFee5.Value = Range("BJ12").Value
In_SetupFee6.Value = Range("BR12").Value
In_SetupFee7.Value = Range("BZ12").Value
In_SetupFee8.Value = Range("CH12").Value
In_SetupFee9.Value = Range("CP12").Value
In_SetupFee10.Value = Range("CX12").Value
In_SetupFee11.Value = Range("DF12").Value
In_SetupFee12.Value = Range("DN12").Value
In_SetupFee13.Value = Range("DV12").Value
In_SetupFee14.Value = Range("ED12").Value
In_SetupFee15.Value = Range("EL12").Value
'Empty In_SetupApplied Fill Boxes
'In_SetupApplied1.Value = Range("AE12").Value
'In_SetupApplied2.Value = Range("AM12").Value
'In_SetupApplied3.Value = Range("AU12").Value
'In_SetupApplied4.Value = Range("BC12").Value
'In_SetupApplied5.Value = Range("BK12").Value
'In_SetupApplied6.Value = Range("BS12").Value
'In_SetupApplied7.Value = Range("CA12").Value
'In_SetupApplied8.Value = Range("CI12").Value
'In_SetupApplied9.Value = Range("CQ12").Value
'In_SetupApplied10.Value = Range("CY12").Value
'In_SetupApplied11.Value = Range("DG12").Value
'In_SetupApplied12.Value = Range("DO12").Value
'In_SetupApplied13.Value = Range("DW12").Value
'In_SetupApplied14.Value = Range("EE12").Value
'In_SetupApplied15.Value = Range("EM12").Value
'Fill with PN In_Times
In_Time1.Value = Range("AA12").Value
In_Time2.Value = Range("AI12").Value
In_Time3.Value = Range("AQ12").Value
In_Time4.Value = Range("AY12").Value
In_Time5.Value = Range("BG12").Value
In_Time6.Value = Range("BO12").Value
In_Time7.Value = Range("BW12").Value
In_Time8.Value = Range("CE12").Value
In_Time9.Value = Range("CM12").Value
In_Time10.Value = Range("CU12").Value
In_Time11.Value = Range("DC12").Value
In_Time12.Value = Range("DK12").Value
In_Time13.Value = Range("DS12").Value
In_Time14.Value = Range("EA12").Value
In_Time15.Value = Range("EI12").Value
'Fill with PN In_Toolings
In_Tooling1.Value = Range("AF12").Value
In_Tooling2.Value = Range("AN12").Value
In_Tooling3.Value = Range("AV12").Value
In_Tooling4.Value = Range("BD12").Value
In_Tooling5.Value = Range("BL12").Value
In_Tooling6.Value = Range("BT12").Value
In_Tooling7.Value = Range("CB12").Value
In_Tooling8.Value = Range("CJ12").Value
In_Tooling9.Value = Range("CR12").Value
In_Tooling10.Value = Range("CZ12").Value
In_Tooling11.Value = Range("DH12").Value
In_Tooling12.Value = Range("DP12").Value
In_Tooling13.Value = Range("DX12").Value
In_Tooling14.Value = Range("EF12").Value
In_Tooling15.Value = Range("EN12").Value
'Set with PN Combo_Tasks
Combo_Task1.Value = Range("Z12").Value
Combo_Task2.Value = Range("AH12").Value
Combo_Task3.Value = Range("AP12").Value
Combo_Task4.Value = Range("AX12").Value
Combo_Task5.Value = Range("BF12").Value
Combo_Task6.Value = Range("BN12").Value
Combo_Task7.Value = Range("BV12").Value
Combo_Task8.Value = Range("CD12").Value
Combo_Task9.Value = Range("CL12").Value
Combo_Task10.Value = Range("CT12").Value
Combo_Task11.Value = Range("DB12").Value
Combo_Task12.Value = Range("DJ12").Value
Combo_Task13.Value = Range("DR12").Value
Combo_Task14.Value = Range("DZ12").Value
Combo_Task15.Value = Range("EH12").Value
'Load Comboboxes with Data
Dim AddItemData
AddItemData = Sheets("Processes").Range("A6:A48").Value
Me.Combo_Task1.List = AddItemData
Me.Combo_Task2.List = AddItemData
Me.Combo_Task3.List = AddItemData
Me.Combo_Task4.List = AddItemData
Me.Combo_Task5.List = AddItemData
Me.Combo_Task6.List = AddItemData
Me.Combo_Task7.List = AddItemData
Me.Combo_Task8.List = AddItemData
Me.Combo_Task9.List = AddItemData
Me.Combo_Task10.List = AddItemData
Me.Combo_Task11.List = AddItemData
Me.Combo_Task12.List = AddItemData
Me.Combo_Task13.List = AddItemData
Me.Combo_Task14.List = AddItemData
Me.Combo_Task15.List = AddItemData
'Set Focus on In_SubComPN
In_SubComDescribe.SetFocus
End Sub
Last edited by a moderator: