I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub


Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,
It's working out great so far. But...A portion of it needs to change a bit.
Go down to "clean up any existing data" of this portion:

Then plop a button also from the Forms toolbar in a nice location (Freeze
Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range


Below here is the part (i think) that is deleting the contents of the
database list after I send the parts I want to order to the order form - and,
I want to keep that info safe.

With Worksheets("PartsSheet1")
Set DestCell = .Range("b5")
'clean up any existing data
.Range("b5:g65536").ClearContents
End With


So far everything else is working great!


Robb



"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

'Dave,

Ok, well I messed it up somewhere. Now it won't copy anything to the order
form!

Robb

"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

This is the portion that cleans up existing data.

With Worksheets("PartsSheet1")
Set DestCell = .Range("b5")
'clean up any existing data
.Range("b5:g65536").ClearContents
End With

But since the data is refreshed each time you click the button, then it seems
like a good thing to do.

If you wanted to just add more stuff to the bottom of the list, you could have
the macro do that--but it won't clean up the values that are no longer
requested.

I guess I'm confused about what you really want to do--especially if there are
changes in the requirements (checkboxes).

Robb27 wrote:

Hi Dave,
It's working out great so far. But...A portion of it needs to change a bit.
Go down to "clean up any existing data" of this portion:

Then plop a button also from the Forms toolbar in a nice location (Freeze
Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range


Below here is the part (i think) that is deleting the contents of the
database list after I send the parts I want to order to the order form - and,
I want to keep that info safe.

With Worksheets("PartsSheet1")
Set DestCell = .Range("b5")
'clean up any existing data
.Range("b5:g65536").ClearContents
End With

So far everything else is working great!

Robb

"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

What happens?

Robb27 wrote:

'Dave,

Ok, well I messed it up somewhere. Now it won't copy anything to the order
form!

Robb

"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

It worked! Ingore the prior posts. I just keep changing things until I got
it right.

This:
With Worksheets("PartsSheet1")

Had to be changed to this:
With Worksheets("orderform")

It was just referencing the wrong sheet. (I bumbled into it. proud of me!)

Thanks for all your time you put into this group!! - I appreciate you.

Robb

Dave,

I don't know if my last post made it to the board, but I got it working!
Ignore the prior posts. I just kept messing with it until I got it right. I
only had to change one thing. Thanks for all your help. Your a credit to the
board.

Robb

"Robb27" wrote:

'Dave,

Ok, well I messed it up somewhere. Now it won't copy anything to the order
form!

Robb

"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Glad you got it working.

Robb27 wrote:

Dave,

I don't know if my last post made it to the board, but I got it working!
Ignore the prior posts. I just kept messing with it until I got it right. I
only had to change one thing. Thanks for all your help. Your a credit to the
board.

Robb

"Robb27" wrote:

'Dave,

Ok, well I messed it up somewhere. Now it won't copy anything to the order
form!

Robb

"Dave Peterson" wrote:

I think I'd take a slightly different approach.

You can still use the checkboxes to indicate that the row should be copied--but
add a button that says "Copy Selected Rows to Order Sheet" (or something like
that).

Then when you hit that button, it'll look at the checkboxes and decide to
copy|paste to the next available row of the order form.

I'd use a linked cell for each checkbox--it makes it easier to check and just
copy the rows you want.

And even though you used a checkbox from the Control Toolbox toolbar, I'd
replace them with checkboxes from the Forms toolbar. I find that they behave
nicer on a worksheet--especially when there are lots of them.

If you want to try (against a copy of your workbook??), you can manually delete
those existing control toolbox toolbar, then add the Forms toolbar checkboxes to
the cells with a macro like:

Option Explicit
Sub RunOnce()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for setting up
For Each myCell In ActiveSheet.Range("G2:G21").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Adjust the range--I used G2:G21--to match your rows.

Then plop a button also from the Forms toolbar in a nice location (Freeze Row 1,
make it higher than normal and put it there so it's always visible???)

Then assign that button a macro like this:

Sub DoTheTransfer()

Application.ScreenUpdating = False

Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsSheet1")
Set DestCell = .Range("a1")
'clean up any existing data
.Range("a1:F65536").ClearContents
End With

With ActiveSheet
Set myRng = .Range("g2", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If myCell.Value = True Then
.Cells(myCell.Row, "A").Resize(1, 6).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'get ready for next row
Set DestCell = DestCell.Offset(1, 0)
End If
Next myCell
End With

With Application
.ScreenUpdating = True
.CutCopyMode = False
End With

End Sub

I think I'd even make it easy to select all the checkboxes (and to clear all the
checkboxes) with buttons forms toolbar (also over Row 1):

Sub ClearAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to clear all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = False
End If
End Sub
Sub SelectAllCheckboxes()
Dim Resp As Long
Resp = MsgBox _
(Prompt:="Are you sure you want to select all the checkboxes?", _
Buttons:=vbYesNo)
If Resp = vbYes Then
ActiveSheet.CheckBoxes.Value = True
End If
End Sub


Robb27 wrote:

Dave,
Here is my (products database) it has 2 sheets, 1 is the database and 1 is
the order form. The database sheet looks like this:

Col A Col B Col C Col D Col E
Col F Col G
ITEM PART Description Unit Package Other
Info (CkBx_1)
NUMBER NUMBER Price Quantity

1 123456 hammer, claw type $2,000.00 2 It's a
hammer!

(1500 rows follow in same fashion)

A customer orders a part by checking the checkbox in col G on the database
sheet, it sends the row (cols A-F) to an another sheet (order form). (I don't
want the checkbox to go to the order form, just the part information).
The order form has to be dynamic because the customer might change their
mind and delete a particular part from the order form by unchecking the box
on the database sheet. (the database sheet is the only customer interactive
part of the workbook.)
It would be ok if the row stayed there on the order form, and empy, as long
as the next part that the customer ordered took the place of the part they
deleted. I don't want empty rows between parts on the order form. I hope
this explains things because i'm stuck...short of sending you the workbook
for your inspection. Thanks for your patients!

Robb

"Dave Peterson" wrote:

I'm confused.

It sure looks like you really want to use .clearcontents. That way the row will
stay, but will be empty.



Robb27 wrote:

Dave,
That worked beautifully, for the first row on the new sheet, but...something
I didn't think of is happening.
Ok:
When it deletes the row and moves all the other rows up one...it changes the
range for the other checkboxes below the one we just deleted. Now, the remove
code you gave me works...but doesn't work because when the row moved up, it's
physical location changed in the sheet, now the code points to a different
row (one below). Can it check to see where it is before deleting and then
delete the row? I hope that makes sense. Here is the code I'm using:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A3:F3").Copy
Worksheets("OrderForm").Range("A2")
Else
Worksheets("Orderform").Range("a2").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A4:F4").Copy
Worksheets("OrderForm").Range("A3")
Else
Worksheets("Orderform").Range("a3").Resize(1, 6).EntireRow.Delete
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A5:F5").Copy
Worksheets("OrderForm").Range("A4")
Else
Worksheets("Orderform").Range("a4").Resize(1, 6).EntireRow.Delete
End If
End Sub

Thanks Dave. I hope I'm painting the picture properly.

Robb

"Dave Peterson" wrote:

change this:
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
to
worksheets("Orderform").range("a1").entirerow.dele te



Robb27 wrote:

Dave,
That works, but I was hoping it would delete the entire row. That way it
would bump the other rows below it up one row. I tried changing the
"clearcontents" to deleterow, but that didn't work.
Thanks Dave

Robb

"Dave Peterson" wrote:

Maybe...

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy _
Worksheets("OrderForm").Range("A1")
else
worksheets("Orderform").range("a1").resize(1,6).cl earcontents
End If
End Sub

Robb27 wrote:

I have an order form. Users check a box next to an item and it copies the
item to another page (the form). I want them to be able to uncheck the box
and have it "undo" what the checkmark does. (deletes the item order for that
item).

Here is the code I'm using to move the item to the order form.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Worksheets("PartsSheet1").Range("A2:F2").Copy
Worksheets("OrderForm").Range("A1")

End If
End Sub

Thank you.

Rob

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson