user form Check box, recheck with code

M

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
73
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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Mikal said:
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 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
Click to expand...


Can you please comment out the code i quoted jsut above using the ' and try using my code instead? This REALLY needs cleaned up so that is more readable and debuggable. I will guide you through the whole process.



Try this instead of what you have and let me know how it works for you...

Code: 
    If Checkbox1 Then
        MsgBox ("Purchased Component - No Operations Permitted.")
    End If
    For Each Ctl In Userform1.Controls   'You need to edit "Userform1" to match your userform with the combo boxes on it.
        If Checkbox1 Then
            If TypeName(Ctl) = "ComboBox" Then
                With Ctl
                    .Clear
                    .AddItem "-"
                    .ListIndex = 0
                    .List = Sheets("Processes").Range("A6").Value
                End With
            End If
        Else
            If TypeName(Ctl) = "ComboBox" Then
                With Ctl
                    .Clear
                    .AddItem "-"
                    .ListIndex = 0
                    .List = Sheets("Processes").Range("A6:A48").Value
                End With
            End If
        End If
    Next Ctl
 
Last edited:
Upvote 0
Hi Steve, Thanks for the reply, I am at it again. All I am really trying to do is get the check box on my userform to be checked when the form opens if the cell in that row has a value greater than "0". Pretty sure I did not load this Code correctly. Not sure what comment out the code means so I replaced it. This is what I get.[COOption Explicit
Dim ws As Worksheet


Private Sub Cancel_Click()
Unload Me
End Sub
Private Sub CheckBox1_Click()
If CheckBox1 Then
MsgBox ("Purchased Component - No Operations Permitted.")
End If
For Each Ctl In StartHereSubCom.Controls 'The CTL highlights and it says "variable not defined"
If CheckBox1 Then
If TypeName(Ctl) = "ComboBox" Then
With Ctl
.Clear
.AddItem "-"
.ListIndex = 0
.List = Sheets("Processes").Range("A6").Value
End With
End If
Else
If TypeName(Ctl) = "ComboBox" Then
With Ctl
.Clear
.AddItem "-"
.ListIndex = 0
.List = Sheets("Processes").Range("A6:A48").Value
End With
End If
End If
Next Ctl

Private Sub ClearForm_Click()
Call UserForm_Initialize
CheckBox1.Value = True
End SubDE][/CODE]
 
Upvote 0
Hi Steve,
I think I am on the right track. I have inserted code that rechecks the box,however it is only looking at one cell, Can you show me how to make it look at that cell in that line. I try to put in a range then I get errors. Here is the code I have


[CO

If Sheet5.Range("I7").Value > 0 Then
CheckBox1.Value = True

Else

CheckBox1.Value = False

End If

DE][/CODE]



 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Assistance changing VBA code to display as HTML or picture of a selection in Lotus Notes
Replies
2
Views
238
jularobert
J
S
  • Solved
Tweak to VBA email code
Replies
3
Views
808
rollis13
rollis13
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
118
RoryA
RoryA
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
139
6StringJazzer
6StringJazzer
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
97
Denny57
D

Forum statistics

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