Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 Thanks, Peter, that worked quite well, however I'm not able to get a hold of the section headings and copy them over. James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
wrote in message
... that worked quite well, however I'm not able to get a hold of the section headings and copy them over. Not sure if that's a question but if so need to give more details. Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
On Wednesday, June 15, 2016 at 3:38:40 AM UTC-5, Peter T wrote:
wrote in message ... that worked quite well, however I'm not able to get a hold of the section headings and copy them over. Not sure if that's a question but if so need to give more details. Peter T Yes, it is a question. I went back and added checkboxes in the column in front of the section heading and checked them. I believe the issue is that they are not in order and the index is not in the correct spot. I haven't had a chance to correct it, yet, but I believe that is the issue. Would there be an easier way of doing it? James |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
wrote in message ... On Wednesday, June 15, 2016 at 3:38:40 AM UTC-5, Peter T wrote: wrote in message ... that worked quite well, however I'm not able to get a hold of the section headings and copy them over. Not sure if that's a question but if so need to give more details. Peter T Yes, it is a question. I went back and added checkboxes in the column in front of the section heading and checked them. I believe the issue is that they are not in order and the index is not in the correct spot. I haven't had a chance to correct it, yet, but I believe that is the issue. Would there be an easier way of doing it? James ======================= Not sure what you mean by "index not in the correct spot" ? If you are referring to the row/col pair I suggested you return from the checkbox's TopLeftCell, note topleft means exactly what it says. Checkboxes tend to get positioned in the row above the row it mainly sits in, and/or perhaps slightly to the left of most of the column it's in. In either case you'd get the wrong row or column index relating to the cell it relates to and your copy will appear to fail. It might be better to define LinkedCell properties, then when looping checkboxes refer to that rather than it's 'rough' position to ID it, or give each checkbox a unique meaningful name, eg "CBox_006" and parse the 006 to id it. OTH, if you mean the index of the checkbox in the checkboxes collection, that will be the ZOrder position, typically the order created. Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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! James |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
Not to discount Peter's sage advise...
FWIW When I make forms I start with a blank sheet setup like graph paper so I can use cells as checkboxes or disguise them as buttons. This makes the form orders of magnitude more stable/reliable/efficient, especially when the form uses outlines to expand/collapse sections. Code for checkbox cells is put in event code behind the worksheet wherein 'Target' address is evaluated in a Select Case construct to match the checkbox cell's named range. Event code also places a checkmark (via Wingdings2 font) in the cell whenever anything is entered. (I instruct to use the spacebar) This approach has totally eliminated 'sheet control hell', and enables making interactive forms with fantastic ease! Let me know if you're interested to learn more... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 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. 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
On Wednesday, June 15, 2016 at 6:30:00 PM UTC-5, GS wrote:
Not to discount Peter's sage advise... FWIW When I make forms I start with a blank sheet setup like graph paper so I can use cells as checkboxes or disguise them as buttons. This makes the form orders of magnitude more stable/reliable/efficient, especially when the form uses outlines to expand/collapse sections. Code for checkbox cells is put in event code behind the worksheet wherein 'Target' address is evaluated in a Select Case construct to match the checkbox cell's named range. Event code also places a checkmark (via Wingdings2 font) in the cell whenever anything is entered. (I instruct to use the spacebar) This approach has totally eliminated 'sheet control hell', and enables making interactive forms with fantastic ease! Let me know if you're interested to learn more... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus Thanks, Garry, I appreciate your help. I'm not a programmer, so your a little beyond my capabilities! James |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
On Wednesday, June 15, 2016 at 6:30:00 PM UTC-5, GS wrote:
Not to discount Peter's sage advise... FWIW When I make forms I start with a blank sheet setup like graph paper so I can use cells as checkboxes or disguise them as buttons. This makes the form orders of magnitude more stable/reliable/efficient, especially when the form uses outlines to expand/collapse sections. Code for checkbox cells is put in event code behind the worksheet wherein 'Target' address is evaluated in a Select Case construct to match the checkbox cell's named range. Event code also places a checkmark (via Wingdings2 font) in the cell whenever anything is entered. (I instruct to use the spacebar) This approach has totally eliminated 'sheet control hell', and enables making interactive forms with fantastic ease! Let me know if you're interested to learn more... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus Thanks, Garry, I appreciate your help. I'm not a programmer, so your a little beyond my capabilities! James If you already use code then you won't have much trouble working from a sample. The code is boilerplate... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox Quandry
On Thursday, June 16, 2016 at 4:35:01 PM UTC-5, Peter T wrote:
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 Thanks, Peter. I resized the rows and the checkboxes and that cleared up a lot of the issues. I will play with it some more and get back to you, if I have any additional questions. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
100% quandry | Excel Discussion (Misc queries) | |||
100% quandry | Excel Programming | |||
COUNTIF quandry | Excel Worksheet Functions | |||
Macro Quandry | Excel Discussion (Misc queries) | |||
SQL Query Quandry | Excel Programming |