He everyone, I'm progressing through my first major VBA project, I have multiple checkboxes to select a shoe type which will then use the combobox values to write each size with the data on a new line. I'm trying to implement this on a commandbutton (commandbuttonapply) but can only get it to insert the first one. The final project will have about 67 checkboxes.

Private Sub CommandButtonApply_Click()
  
  Call Checkboxes



End Sub


Sub Checkboxes()
If Me.CheckBox9k.Value = True And Me.CheckBox95k.Value = True Then
    Else
    If Me.CheckBox9k.Value = True Then
    Call CheckBox9ktrue
    
    ElseIf Me.CheckBox95k.Value = True Then
    Call checkbox95ktrue
          
                    Exit Sub
End If
End If




  End Sub
    
    Sub checkbox95ktrue()

'''Input
        
    Dim ws As Worksheet
    Dim LastRow As Long, RowInsert As Long
    
    Set ws = ThisWorkbook.Worksheets("stock")
     With ws
        LastRow = .Cells(Rows.Count, "A").End(xlUp).row
        
        RowInsert = .Range("A1:A" & LastRow).Find("*", .Cells(LastRow, "A"), xlValues, , , xlPrevious).row
        RowInsert = RowInsert   1
        RowInvert = RowInsert - 1
        
        
    
    'add the uk size input code here
    
  '''Checkbox based search
 
 ''Start
  
    
        
        If Me.comboboxbrand.Value = "" Then
    MsgBox "please enter a brand", vbInformation
        Exit Sub
        End If
        
    If Me.comboboxgender.Value = "" Then
    MsgBox "please enter an item gender", vbInformation
        Exit Sub
        End If
        
    If Me.comboboxclosure.Value = "" Then
    MsgBox "please enter a closure type", vbInformation
        Exit Sub
        End If
        
    If Me.comboboxmaterial.Value = "" Then
    MsgBox "please enter an upper material type", vbInformation
        Exit Sub
        End If
        
    'If Me.comboboxuksize.Value = "" Then
    'MsgBox "please enter the uk size", vbInformation
        'Exit Sub
        'End If
        
  
        
     If Me.comboboxmodel.Value = "" Then
    MsgBox "please enter a model type", vbInformation
       
        
        
        ElseIf Me.CheckBox9k.Value = True Then
    
   

                                         ''''This has to match the number of rows input below
        .Cells(RowInsert, "A").Resize(1, 8).Value = Array( _
            Me.txtDate.Text, _
            Me.textboxparentsku.Text, _
            Me.comboboxbrand.Text, _
            Me.comboboxclosure.Text, _
            Me.comboboxgender.Text, _
            Me.comboboxmaterial.Text, _
            Me.comboboxmodel.Text, _
            Me.ComboBoxcolour.Text _
        )
        ws.Range("I" & RowInsert).Value = CheckBox95k.Caption
        
        'ElseIf Me.CheckBox9k.Value = False Then .Cells(RowInvert, "A").Resize(1, 9).Value = ""
                                                
    

End If
''Finish

 

    
   
    
    
    Set ws = Nothing


End With

End Sub



Sub CheckBox9ktrue()

'''Input
    
Dim ws As Worksheet
Dim LastRow As Long, RowInsert As Long

Set ws = ThisWorkbook.Worksheets("stock")
 With ws
    LastRow = .Cells(Rows.Count, "A").End(xlUp).row
    
    RowInsert = .Range("A1:A" & LastRow).Find("*", .Cells(LastRow, "A"), xlValues, , , xlPrevious).row
    RowInsert = RowInsert   1
    RowInvert = RowInsert - 1
    
    

'add the uk size input code here

  '''Checkbox based search
 
 ''Start



    
    If Me.comboboxbrand.Value = "" Then
MsgBox "please enter a brand", vbInformation
    Exit Sub
    End If
    
If Me.comboboxgender.Value = "" Then
MsgBox "please enter an item gender", vbInformation
    Exit Sub
    End If
    
If Me.comboboxclosure.Value = "" Then
MsgBox "please enter a closure type", vbInformation
    Exit Sub
    End If
    
If Me.comboboxmaterial.Value = "" Then
MsgBox "please enter an upper material type", vbInformation
    Exit Sub
    End If
    
'If Me.comboboxuksize.Value = "" Then
'MsgBox "please enter the uk size", vbInformation
    'Exit Sub
    'End If
    

    
 If Me.comboboxmodel.Value = "" Then
MsgBox "please enter a model type", vbInformation
   
    
    
    ElseIf Me.CheckBox9k.Value = True Then



                                     ''''This has to match the number of rows input below
    .Cells(RowInsert, "A").Resize(1, 8).Value = Array( _
        Me.txtDate.Text, _
        Me.textboxparentsku.Text, _
        Me.comboboxbrand.Text, _
        Me.comboboxclosure.Text, _
        Me.comboboxgender.Text, _
        Me.comboboxmaterial.Text, _
        Me.comboboxmodel.Text, _
        Me.ComboBoxcolour.Text _
    )
    ws.Range("I" & RowInsert).Value = CheckBox9k.Caption
    
    'ElseIf Me.CheckBox9k.Value = False Then .Cells(RowInvert, "A").Resize(1, 9).Value = ""
                                            


End If
''Finish







Set ws = Nothing


End With


End Sub

CodePudding user response：

You don't need to have a sub for every check box, you can use Me.Controls(variable)

Option Explicit

Private Sub CommandButtonApply_Click()
    Call Checkboxes
End Sub

Sub Checkboxes()

    Dim ar, data(8) As String, n As Integer, i As Integer, msg As String
    ar = Array("brand", "closure", "gender", "material", "model", "colour")
    
    ' validate inputs
    data(0) = Me.TxtDate.Text
    data(1) = Me.TextBoxparentsku.Text
    For n = 0 To UBound(ar)
        data(n   2) = Me.Controls("ComboBox" & ar(n)).Value
        If data(n   2) = "" Then
            msg = msg & vbCrLf & ar(n)
        End If
    Next

    ' any blanks
    If msg <> "" Then
        MsgBox "Please enter a value in :" & msg, vbExclamation
        Exit Sub
    End If

    ' count number of checkboxes checked
    Dim arChk, s As String
    arChk = Array("9k", "95k")
    For n = 0 To UBound(arChk)
        s = "CheckBox" & arChk(n)
        If Me.Controls(s).Value = True Then
            data(8) = Me.Controls(s).Caption ' Col I
            i = i   1
        End If
    Next

    ' only 1 allowed
    If i = 0 Then
        MsgBox "No checkbox", vbExclamation
        Exit Sub
    ElseIf i > 1 Then
        MsgBox "More than 1 checkbox", vbExclamation
        Exit Sub
    End If

    ' update worksheet
    Dim ws As Worksheet, LastRow As Long
    Set ws = ThisWorkbook.Worksheets("stock")
    With ws
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Cells(LastRow   1, "A").Resize(1, 9).Value = data
    End With
End Sub