Checkbox to copy row to new sheet

[ADAMC]

Hi all,
Thanks in advance for any help.
I have 4 sheets
Sheet 1
Sheet 2
Sheet 3
"Checkout"

What i would like:
Rows of data on Sheets 1/2/3 with a checkbox in column A of each row
I check a couple of checkboxes on sheet 1 some on sheet 2 and some on sheet 3
where i put a tick in checkbox they have copied across to next available row in sheet "checkout"

If i tick a checkbox then untick it (maybe changed mind) copied row is removed from checkout sheet.
Any help greatly appreciated i know the basics but not enough to get me going.
Im assuming it will take VBA but i could be wrong (an on this site i usally am)

THANKS!

 

[phxsportz]

This should get you started..

Check the box and it will paste it to the CHECKOUT, Uncheck the box and it will remove it from the checkout sheet..

Private Sub CheckBox1_Click()
    With Sheets("Checkout")
        ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
        If CheckBox1 Then
            Range("B1").Copy   '(Obviously you'll need to adjust this range to your needs
            .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

 

[ADAMC]

Thats a good start and it worked great thankyou!

Can i just clarify something please?

Im guessing i would have to put this code in the checkbox click event for every checkbox and change the copied range from B to C/ C to D an so on?

Thanks

 

[phxsportz]

Yes, but you can significantly reduce the coding by building a chkbox processor, and then passing the chkbox # to it..

Gimme a sec and I'll get a sample for you from a sheet that I built awhile back...

 

[phxsportz]

This was from a userform that I developed, but the principle is the same..

Private Sub CheckBox1_Change()
  Call CheckBox_Handler(1)
End Sub
Private Sub CheckBox2_Change()
  Call CheckBox_Handler(2)
End Sub
Private Sub CheckBox3_Change()
  Call CheckBox_Handler(3)
End Sub

Actually have 48 chk boxes, but you get the idea...

And the Handler code is...

Private Sub CheckBox_Handler(arg1)
  If Not bt_Cancel.Enabled Then Exit Sub   'If cancelling edit or add, don't evaluate
  If Controls("CheckBox" & arg1) Then 'Over Ride ?
    Controls("cb_liqname" & arg1).Enabled = True
    Controls("cb_reason" & arg1).Enabled = True
    Controls("tb_bottles" & arg1).Enabled = True
  Else
    Controls("cb_liqname" & arg1).Enabled = False
    Controls("cb_reason" & arg1).Enabled = False
    Controls("tb_bottles" & arg1).Enabled = False
  End If
End Sub

 

[ADAMC]

Many thanks

Im going to be honest and say although i see the concept i have not a clue what im doing but did understand the first code you posted

Im going to have a play with the code you have given me, see if i can come up with something.

To explain more to yourself and anyone else im am trying to replicate a shopping basket.

My plan was tick the checkbox next to a product which passes to the checkout sheet.
The user can then click 'place order' on the checkout sheet which sends just that sheet to me with the prroducts where the checkboxes were checked.

If anyone has a better or similar idea id be glad to hear it

At least now (thanks to you) i can mess around with the checkbox click/ change events and see what i can come up with!!

Thanks for your prompt and helpful replies

 

[phxsportz]

This might help clear up the handler portion of the code
When the check box is clicked it triggers a CHANGE EVENT
within the event procedure Private Sub CheckBox1_Click() it executes the following command..
Call CheckBox_Handler(1) This calls the handler procedure and passes the number "1" to it indicating that Checkbox1 was the checkbox that was clicked. In the Handler procedure the "1" is represented as ARG1 (the passed variable).
*note that either Click or Change can be used as the event trigger

Private Sub CheckBox_Handler(arg1)
'===================================================================
' Arg1 is the variable containing the chkbox # that was changed
'===================================================================
    With Sheets("Checkout")
        ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
        If Controls("CheckBox" & arg1) Then     'if arg1 equals 1, then this will make
                                                'Checkbox1 the control
            Range("B" & arg1).Copy   '(Obviously you'll need to adjust this range to your needs
            .Range("B" & ckrownum).PasteSpecial
        Else
            For x = 1 To ckrownum
                If .Cells(ckrownum, 2) = Cells(arg1, 2) Then
                    .Rows(ckrownum).Delete
                    Exit For
                End If
            Next
        End If
    End With
End Sub

 

[sous2817]

Man phxsportz, where were you when I was desinging a checkbox heavy application for someone! Thanks for the tip...tagging this for future reference!

 

[MickG]

Hi, An alternative.
If each "Forms" check box is "Linked" to its Related row on sheets 1 to 3, then this code will loop through all the Check boxes on each sheet and copy all rows that Have check Boxes checked, to sheet "Checkout".
It it works for you, you could assign each Check Box Click event to run this code.
NB:- Run this code from Sheet ("Checkout")

[COLOR=navy]Sub[/COLOR] MG08Jul25
[COLOR=navy]Dim[/COLOR] shp [COLOR=navy]As[/COLOR] Shape, ws [COLOR=navy]As[/COLOR] Worksheet, msg [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] cop
c = 1
cop = Rows(1).Value
Cells.ClearContents
Rows(1).Value = cop
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] ws [COLOR=navy]In[/COLOR] ActiveWorkbook.Worksheets
[COLOR=navy]If[/COLOR] Not ws.Name = "Checkout" [COLOR=navy]Then[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] shp [COLOR=navy]In[/COLOR] ws.Shapes
        [COLOR=navy]If[/COLOR] shp.Type = msoFormControl [COLOR=navy]Then[/COLOR]
            [COLOR=navy]If[/COLOR] shp.FormControlType = xlCheckBox [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] shp.ControlFormat.Value = 1 [COLOR=navy]Then[/COLOR]
                    c = c + 1
                    ws.Range(shp.ControlFormat.LinkedCell).Resize(, 10).Copy Sheets("Checkout").Cells(c, 1)
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] shp
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] ws
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[ADAMC]

phxsportz thanks very much for the explanation that helps alot.

MickG thanks also how do i link a checkbox to a row?
I thought right click > format control > then setting cell link as 1:1?
this seems to add true/ false behind the checkbox how can i get rid of that?
or am i doing it wrong?

Thanks!!