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