Modify code for vlookup

M

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
73
I have a code that loads a value into 2 text boxes when you choose a part # in t lit box. I would like to modify the code so that it sees the part #in the list box and loads the text box, instead of upon selection of the part #. Can someone shoe me how to make the modification?

Private Sub In_Material_Click()
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
 
Sort by date Sort by votes
I believe this is a sample:
load comb boxes with data
Dim AddItemData
AddItemData = Sheets("procceses").Range ("A6:A48).Value
Me.Combo_Task1.List = AddItemData

There are many of these for the different boxes.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
When you said
However when I am doing an update, if I have changed the values that it is pulling in, it does not update to the new value when the form reloads
Click to expand...
were you referring to the values in the 2 textboxes?
Also when you "reload" the form does the combobox have a value displayed, or is it blank until you select a value from the list?
 
Upvote 0
yes I am referring to the values in the 2 text boxes. When the form reloads it contains the value that originally loaded to the spreadsheet.
 
Upvote 0
So how do you "reload" the form after updating the sheet?
 
Upvote 0
I click on a "command button" Which activates the user form and the codes load the info back onto the form
 
Upvote 0
Please post all the code in the userform.
When posting code please use code tags, the # icon in the reply window.
 
Upvote 0
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_CostPerUnit
In_CostPerUnit.Value = Range("U12").Value
'Fill with PN In_Material2
In_Material2.Value = Range("O12").Value
'Fill with PN In_Quantity2
In_Quantity2.Value = Range("P12").Value
'Fill with PN In_CostPerUnit2
In_CostPerUnit2.Value = Range("W12").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

'Fill with PN In_Units
In_Units.Value = Range("S12").Value
'Fill with PN In_Units2
In_Units2.Value = Range("T12").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:
Upvote 0
The only part that is writting to the sheet is the "CreateSubCom_Click" event. Is this the Update that your refer to?

When the Userform is loaded this line
Code: 
In_Material.Value = Range("Q12").Value
populates the combo, which in turn will trigger the "In_Material_Change" event thus updating the values in the textboxes.
 
Upvote 0
Yes, you are correct, It appears that the problem I was having was because I still had that same code for the two text boxes so it was over riding the change. I erased the 2 lines of code and that was bring it bring in the values to the text boxes, and now that allows your code to work. Thanks so much for your help.

If you have the time, I have one other thing I am trying to accomplish. I have a check box that when checked it calculates a formula and put the data in one column, when not checked it puts the data in another column. I want it to look at the column that is checked and if greater than 0 recheck the check box. Is that possible?
 
Upvote 0
Glad you sorted it & thanks for the feedback.

You will need to start a new thread for your new question.
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hide Buttons Selectively
Replies
3
Views
113
dmt32
D
I
  • Question Question
Listbox columns dont alter when i set the width advice please
Replies
7
Views
135
Dan_W
D
C
  • Question Question
Force user to enter at least one symbol and one number in a textbox through a userform
Replies
9
Views
310
christianbiker
C
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
138
Abdo
A

Forum statistics

Threads
1,224,823
Messages
6,181,170
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