Home > OS >  Vba Stop Data Entry sheet Entering data if checkbox is set to False
Vba Stop Data Entry sheet Entering data if checkbox is set to False

Time:10-01

I'm having an issue with a data entry sheet I am designing. I have kind of hacked together a function that copies the forms data and selected size. I'm trying to have it do this for any size set to true, but currently if it's set to false it is still pasting in the form data on the next line.

This is what the form looks like

'''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

                                         ''''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 _
        )
    
'Checkbox data entry

    If Me.CheckBox0k.Value = True Then ws.Range("I" & RowInsert).Value = "0k"
   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

                                         ''''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 _
        )
    End With
    
    
     If Me.CheckBox05k.Value = False Then ws.Range("I" & RowInsert).Value = "" 

'Thanks Alex, If the above is false I don't want it to print anything


    If Me.CheckBox05k.Value = True Then ws.Range("I" & RowInsert).Value = "0.5k"  
    
Exit Sub
    
    
    
    End With
  
   
    
    
    Set ws = Nothing


End Sub

CodePudding user response:

Right now your if-statements are only applied to the same line. You need to put everything you only want to happen if the if-statement is true inside:

If Me.CheckBox05k.Value = True Then
    'Everything you only want to happen now in here
End If

Additionally, you can also use ElseIf or Else since a checkbox can only be true or false.

So it could be something like this:

If Me.CheckBox0k.Value = True Then
    ws.Range("I" & RowInsert).Value = "0k"
    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

                                         ''''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 _
        )
    End With
ElseIf Me.CheckBox05k.Value = False Then
    ws.Range("I" & RowInsert).Value = ""
Else
    ws.Range("I" & RowInsert).Value = "0.5k"
End If
  • Related