Results 1 to 4 of 4

Thread: user form Check box, recheck with code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2015
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default user form Check box, recheck with code

    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 Fluff; Aug 2nd, 2019 at 01:01 PM. Reason: Added code tags

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: user form Check box, recheck with code

    Quote Originally Posted by Mikal View Post
    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

    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 by Steve_; Aug 2nd, 2019 at 01:17 PM.

  3. #3
    Board Regular
    Join Date
    Aug 2015
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: user form Check box, recheck with code

    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]

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: user form Check box, recheck with code

    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]




Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •