Check box

[lady_alina]

Hi,

I have data in sheet1 and check box in column A against each line. I want to copy the data in next sheet when check box is checked, however the code that I have works only for each check box and it is a very tideous procedure and it doesn't really help much.

What I want is a code that will work for all check boxes (count of check box will be around 5000). The code should identify the available empty row and copy the checked data at once in the next available row in the other sheet. Also headers are not copied so want a solution for that as well. If the checked checkbox is unchecked then the data should be remove from that sheet and row should be deleted or the lines below that should move up. Hope my question makes sense.

Private Sub CheckBox1_Click()    With Sheets("Checkout")
        ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
        If CheckBox1 Then
            Range("B1:D1").copy
            .Range("B" & ckrownum).PasteSpecial
        Else
            For x = 1 To ckrownum
                If .Cells(ckrownum, 2) = Cells(1, 2) Then
                    .Rows(ckrownum).Delete
                    Exit For
                End If
            Next
        End If
    End With
End Sub

 

[lady_alina]

I'm open for different options in vba that does the same thing. Please help with this one.

 

[lady_alina]

Can somebody help please, need to close this urgently...

 

[NoSparks]

lady_alina, this forum is so busy that your question is often off the first page in a matter of moments. Regular users that come here looking to answer questions know this and use the "Zero Reply Posts" button near the top of the page to see what questions are going unanswered. By being the first reply to your own post you are actually reducing the chance of someone willing to assist ever knowing your question even exits.

Now, about those check boxes.... I don't really like check boxes. My prefrence for something like this would be to have a double click in the first column put in a Wingdings 2 upper case P which displays as a check mark. This would fire the Worksheet_Change Event which you can then use to do what you want. I'd use something along the lines of this. Both of these go on the first sheet's sheet module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub
    With Target.Cells
        .Font.Name = "Wingdings 2"
        If .Value = "" Then
            .Value = "P"
        Else
            .Value = ""
        End If
    End With
    Cancel = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DeleteRow As Long
    Dim WriteRow As Long
    
    If Target.Column = 1 Then
        If Target.Value = "P" Then
            'add to other sheet
            WriteRow = Sheets("Checkout").Cells(Rows.Count, "B").End(xlUp).Row + 1
            Target.Cells.Offset(0, 1).Resize(1, 3).Copy Sheets("Checkout").Range("B" & WriteRow)
        Else
            'remove what you don't want
            DeleteRow = Application.WorksheetFunction.Match(Target.Cells.Offset(0, 1).Value, Sheets("Checkout").Range("B1:B6000"), 0)
            Sheets("Checkout").Cells(DeleteRow, 2).EntireRow.Delete
        End If
    End If
End Sub

 

[AlphaFrog]

You could use Form-type checkboxes instead of ActiveX-type checkboxes. Then assign one macro to all the checkboxes. The macro could use the Application.Caller method to determine the calling checkbox and it's row location.

[color=darkblue]Sub[/color] CheckBoxes_Click()
    [color=darkblue]Dim[/color] cb [color=darkblue]As[/color] CheckBox, cbRow [color=darkblue]As[/color] Range
    [color=darkblue]Set[/color] cb = ActiveSheet.CheckBoxes(Application.Caller)
    [color=darkblue]Set[/color] cbRow = cb.TopLeftCell.EntireRow
    [color=darkblue]With[/color] Sheets("Checkout")
        [color=darkblue]If[/color] cb.Value = xlOn [color=darkblue]Then[/color]
            cbRow.Range("B1:D1").Copy
            .Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial
        [color=darkblue]Else[/color]
            [color=darkblue]Set[/color] cbRow = .Columns("B").Find(cbRow.Range("B1"), , , xlWhole, , , [color=darkblue]False[/color])
            [color=darkblue]If[/color] [color=darkblue]Not[/color] cbRow [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] cbRow.EntireRow.Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Overview of forms, Form controls, and ActiveX controls on a worksheet

 

[Smitty]

Application.Caller is an awesome method, but please try to stay away from objects on your sheets (especially 5K of them!) if at all possible.

You're only going to cause nightmares that way (and lots of user support).

 

[AlphaFrog]

Application.Caller is an awesome method, but please try to stay away from objects on your sheets (especially 5K of them!) if at all possible.

You're only going to cause nightmares that way (and lots of user support).

Good point!