wrote in message
...
On Wednesday, June 15, 2016 at 5:05:50 PM UTC-5, Peter T wrote:
wrote in message
...
On Tuesday, June 14, 2016 at 11:24:43 AM UTC-5, Peter T wrote:
wrote in message
I have a spreadsheet with a number of non-contiguous checkboxes (forms
control) in column A. Section headings break up the sequence. In column
B,
there is a description associated with each checkbox. I need to be able
to
check the status of the checkboxes - if checked, copy the description to
a
new sheet. If no checkbox is found, then print the section heading and
continue. The snippet below is as far as I can get before I run into
problems. Any help would be greatly appreciated.

LastRow = curWks.Cells(curWks.Rows.Count, 2).End(xlUp).Row
For iRow = 3 To LastRow
For Each myCBX In curWks.CheckBoxes
If myCBX.Value = xlOn Then
curWks.Cells(iRow, 2).Copy
Destination:=Worksheets("Sheet1").Cells(xCntr + 1, 1)
xCntr = xCntr + 1
End If
Next myCBX
Next iRow


===============================

Couple of comments at just a glance of your code and without testing
anything

Remove the iRow loop, it doesn't relate to the checkboxes so you're
repeating the same the checkbox loop multiple times.

Get the relevant row and column from the TopLeftCell of the given myCBX
object, eg
rowIdx = myCBX.TopLeftCell.Row
colIdx = myCBX.TopLeftCell.Column

You might want to check the position, ie TopLeftCell, is in the
appropriate
cell range, or between 3 and LastRow, and if so do the copy stuff.
Though
rather than "Copy" simply do rngDest.Value = rngSource.Value unless you
also
want the formats

Peter T

Here is how I incorporated what you advised for me.

For Each myCBX In curWks.CheckBoxes
colIdx = myCBX.TopLeftCell.Column
rowIdx = myCBX.TopLeftCell.Row

If myCBX.Value = xlOn Then
curWks.Cells(rowIdx, colIdx).Offset(0, 1).Copy
Destination:=Worksheets("Sheet1").Cells(xCntr + 1, 1)
Debug.Print rowIdx
xCntr = xCntr + 1
End If
Next myCBX

Originally, I had a checkbox on line 3, but subsequently added a checkbox
on
line 2, because I wanted the section header (there are 4 headers) printed
and each of the standards selected (checkboxes) to go under that header.
When I debug it, line 3 still shows up first and line 1 (not line 2) shows
up at the very bottom. Obviously, I'm not doing something correctly!

=================================

Check what's really going on and go from there

For Each myCBX In curWks.CheckBoxes
colIdx = myCBX.TopLeftCell.Column
rowIdx = myCBX.TopLeftCell.Row

With myCBX.TopLeftCell
colIdx = .Column
rowIdx = .Row
s1 = .Address
s2 = .Offset(0, 1).Address
If Len(.Offset(0, 1).Value) Then
v = .Offset(0, 1).Value
Else: v = "empty"
End If

End With

Debug.Print myCBX.Value, rowIdx; colIdx, s1, s2, v

Next myCBX

Peter T


1 3 1 $A$3:$A$3 $B$3:$B$3 EE.RL.3.1
-4146 4 1 $A$3 $B$3 EE.RL.3.1a
-4146 5 1 $A$5 $B$5 EE.RL.3.3
-4146 6 1 $A$6 $B$6 EE.RL.3.5
-4146 7 1 $A$7 $B$7 EE.RI.3.
-4146 7 1 $A$7 $B$7 EE.RI.3.1
-4146 8 1 $A$8 $B$8 EE.RI.3.2
1 9 1 $A$9 $B$9 EE.RI.3.3
1 11 1 $A$11 $B$11 ELA.C1.2
-4146 13 1 $A$13 $B$13 EE.RI.3.
-4146 13 1 $A$13 $B$13 EE.RI.3.4
-4146 14 1 $A$14 $B$14 EE.RI.3.8
1 15 1 $A$15 $B$15 EE.L.3.5.a
1 17 1 $A$17 $B$17 ELA.C1.3
-4146 19 1 $A$19 $B$19 EE.RI.3.9
1 20 1 $A$20 $B$20 ELA.C2.1
-4146 22 1 $A$22 $B$22 EE.W.3.4
-4146 1 1 $A$1 $B$1 empty

Thanks, Peter. I ran your code and found some interesting issues. First,
some of the checkboxes were on the same row, which caused problems with the
line count (the debug is below). Thus, when the row and column index
variables returned the same values for different rows, it mis-read the
checkboxes. Row 10, column 1 and row 12, column 1 were checked, the debug
reads the wrong row and the offset (standard) printed is for the previous
row. Inserting and checking the box at the top of the page(row 2, column 1)
appears at the bottom of the debug and no offset data. I'm not sure how to
proceed from here, but will play with it some more.

================================

Checkboxes can easily end up on the same row, ie the lower one just in the
bottom of the row above, as I said before "Checkboxes
tend to get positioned in the row above the row it mainly sits in".

Typically checkboxes need a taller row height that default.

The last checkbox with row-1 in the debug is almost certainly the last one
that got added to the sheet, where it's located is irrelevant, not a problem
as long you understand. If it niggles you just send it to the back and next
time it'll be the first in the collection. Except it's in row-1 not row-2
even if it appears to be.

But some of that debug makes no sense. You've got 2 boxes in row A7, both
offset(0,1) cells are B7 as expected, yet the two B7 values are different -
they can't be! Similarly the pair in A13. Or did a last digit get truncated
in posting perhaps..

A simple macro would line up the boxes, might be worth doing, but look into
using LinkedCell as I suggested, and/or uniquely name each box and id it
that way as I suggested before.

Peter T