Delete row if check box is switched to false

[Sirico]

​

I hope your weeks are going well.
Currently writing a data entry from in a VBA user form,
It will be using an array of checkboxes to select size which then fills a row with the other data provided when that checkbox is ticked.
I'm currently running into an issue where I don't know what code to run to have the function delete its previous data when the checkbox is unticked.
Thank you for looking at my post

Current UI with tickboxes

Snipet of the code with current attempt to delete row if false

Private Sub CheckBox0k_Click()
    '''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

        'add the uk size input code here
    
        '''Checkbox based search
        ''Start

        If Me.CheckBox0k.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.textboxsku.Text, _
                Me.comboboxbrand.Text, _
                Me.comboboxclosure.Text, _
                Me.comboboxgender.Text, _
                Me.comboboxmaterial.Text, _
                Me.comboboxmodel.Text _
            )
            ws.Range("I" & RowInsert).Value = CheckBox0k.Caption   
        



'This is the code I'm having issues with
ElseIf CheckBox0k.Value = False Then   
            .Cells(RowInsert, "A").Resize(1, 8).Value = ws.Range("I" & RowInsert).Value = ""
        End If

        ''Finish
        Set ws = Nothing    
    End With 
End Sub

 

[NoSparks]

Are you sure when a check box gets checked or unchecked is the right time for data to be written to or removed from the sheet?

Wouldn't the purpose of the 'Add' button be to write to the sheet for each checkbox that is checked?

 

[Sirico]

This was the easiest way my current skill level could think of to enter each line dependant on the shoe size. I have currently removed the add button.

 

[NoSparks]

This was the easiest way my current skill level could think of to enter each line dependant on the shoe size.

Easiest to think of, not to implement without using a class module and some kind of boolean for resets.

I have currently removed the add button.

I wouldn't, I'd let the customer check and uncheck as much as they want and only write the data to the sheet when they click the 'add' button.
I set up a simple userform similiar to your picture and used the below code
See if something like this would work for you

Private Sub AddButton_Click()
    Dim ws As Worksheet, writerow As Long, sizeStr As String
    Dim ctrl As Control, i As Long, ray As Variant

'check that all text and combos are populated
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            If ctrl.Value = "" Then
                ctrl.SetFocus
                MsgBox "More info required"
                Exit Sub
            End If
        End If
    Next ctrl

' assemble string of sizes, start blank
    sizeStr = ""
    'loop through controls in UserForm
    For Each ctrl In Me.Controls
    'if control is a CheckBox
     If TypeName(ctrl) = "CheckBox" Then
        'check for being checked
        If ctrl.Value = True Then
        'add the caption to the variable sizeStr
            sizeStr = sizeStr & "|" & ctrl.Caption
        End If
     End If
    Next ctrl
    
' if none checked then exit here
    If Len(sizeStr) = 0 Then Exit Sub
    
' if some checked split sizeStr into an array
    ray = Split(Mid(sizeStr, 2), "|")
    
' the worksheet to work on
    Set ws = Sheets("stock")

' write to next row on sheet for each checked checkbox
    With ws
        For i = LBound(ray) To UBound(ray)
            writerow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            .Range("A" & writerow).Resize(, 9).Value = Array( _
                    Me.txtDate.Text, _
                    Me.textboxparentsku.Text, _
                    Me.textboxsku.Text, _
                    Me.comboboxbrand.Text, _
                    Me.comboboxclosure.Text, _
                    Me.comboboxgender.Text, _
                    Me.comboboxmaterial.Text, _
                    Me.comboboxmodel.Text, _
                    ray(i) _
                    )
        Next i
    End With
End Sub

my test file Box

 

[Sirico]

Thank you so much for the reply, I have re-instated the apply button and currently have it using call functions. Thank you for the commented code super useful to learn from I'll post on how I get on.

 

[Sirico]

Hey I just ran your version of what I'm trying to do. It's super impressive how functional it is compared to my current implementation that's nearing lord of the rings for it's character count. I've got a long road of learning ahead hoping I can get to your levels of efficiency. Thank you again for taking the time to go through it with me.

 

[NoSparks]

Glad I could help. Thanks for the feedback.