Solved: Loop for Value True/ Copy 4 cells to other sheet

[kiyiya]

I am wanting to loop through 2 columns and check for a true value and then copy 4 cells from that row (4 columns side by side) to another sheet only if the range of say 20-37 is blank.

It is going to be a sales receipt but I only want to print out what the customer will purchase and nothing else.

See attached for my idea of what it is supposed to look like.

Thanks in advance!

[Bob Phillips]

Public Sub CreateInvoice()
Dim chk As CheckBox
Dim NextRow As Long
Dim i As Long

NextRow = 20

With Worksheets("Sales Receipt")

.Range("A20:D37").ClearContents
For Each chk In Worksheets("Data").CheckBoxes

If chk.Value = 1 Then

For i = 1 To 4

.Cells(NextRow, i).Value = chk.TopLeftCell.Offset(0, i).Value
Next i

NextRow = NextRow + 1
End If
Next chk
End With
End Sub

[david000]

xld,
I'm trying to understand this. What is the logic in excel for the TopLeftCell to be one cell to the right of the checkbox?

[Bob Phillips]

It is just finding the cell below the TopLeft corner of the checkbox, just to get a cell as checkboxes are not in a cell. As long as the OP is very disciplined in how he sets hi checkboxes, it works.

[kiyiya]

XLD,

First off, thanks! It works just like I wanted. When you said
As long as the OP is very disciplined in how he sets hi checkboxes, it works. do you mean setting them within the parameters of a cell or the cell the checkbox uses for it's value? I am a new to VBA so be gentle please.:doh:

[Bob Phillips]

No, it is all to do with the spreadsheet setup. As long as your checkboxes are all carefully placed with the top left of the checkbox IN the cell left of the first data cell, that code will work. In your example, it was beautifully disciplined, as long as it stays like that the code works.

[kiyiya]

XLD,

I see what you mean about the checkbox position! On my "real datasheet" I need for the loop to start on the first column of the sheet (column B) and then look at the second column (column G) and transfer them in order. I was using additional checkboxses to "do other things" but your code transfers them to the charges section of the receipt as well. I do appreciate the code and have learned a real timesaver with it!

Any suggestions?

[Bob Phillips]

Are you up for naming the checkboxes in the correct order with a structured naming style, such as CB1, CB2, Cb3, etc.?

[kiyiya]

I tried that with a few of them to see if it helps but it did not seem to matter.

[Bob Phillips]

If you do use such a naming structure, you need modified code



Public Sub CreateInvoice()
Dim chk As CheckBox
Dim NextRow As Long
Dim i As Long

NextRow = 20

With Worksheets("Sales Receipt")

.Range("A20:D37").ClearContents
i = 1
Do

Set chk = Nothing
On Error Resume Next
Set chk = Worksheets("Data").CheckBoxes("CB" & i)
On Error GoTo 0

If Not chk Is Nothing Then

If chk.Value = 1 Then

For i = 1 To 4

.Cells(NextRow, i).Value = chk.TopLeftCell.Offset(0, i).Value
Next i

NextRow = NextRow + 1
End If
End If

i = i + 1
Loop Until chk Is Nothing
End With
End Sub

[kiyiya]

This code seems to only pick up the first check box unless I uncheck it. At that point it will pick up the second checkbox but only the second on and no other. When I comment out the line "On Error Resume Next" I get the following message when I run the code: "Unable to get the Checkboxes property of the Worksheet class".

[Bob Phillips]

Sorry, my mistake, I re-used the i variable.

BTW, I couldn't rename the checkbox CB1 as it is a cell reference, so I used CB_1 etc.



Public Sub CreateInvoice()
Dim chk As CheckBox
Dim NextRow As Long
Dim i As Long
Dim j As Long

NextRow = 20

With Worksheets("Sales Receipt")

.Range("A20:D37").ClearContents
i = 1
Do

Set chk = Nothing
On Error Resume Next
Set chk = Worksheets("Data").CheckBoxes("CB_" & i)
On Error GoTo 0

If Not chk Is Nothing Then

If chk.Value = 1 Then

For j = 1 To 4

.Cells(NextRow, j).Value = chk.TopLeftCell.Offset(0, j).Value
Next j

NextRow = NextRow + 1
End If
End If

i = i + 1
Loop Until chk Is Nothing
End With
End Sub

[kiyiya]

Yea, I figured out that one when I tried it; took me right to a cell. I tried adding a new variable as well because I was having trouble following the code but I did it wrong. Thanks for sticking with me on this.

When I run this code CB_1 shows up twice. When I uncheck CB_1 and check CB_2 then CB_1 shows up once. No other checkboxes show up at all. When I turn off debugging I get the same error as before: "Unable to get the Checkboxes property of the Worksheet class".

[kiyiya]

XLD,

Any ideas on how to get this code to work or perhaps looking at the column values as true instead of the checkbox object as 1?

[Bob Phillips]

Sorry, I thought it was working. So what does it do/not do?

[kiyiya]

It looks like the loop only picks up the first checkbox (CB_1). When I check CB_1 and CB_2 it lists the same data on separate lines but when CB_2 is checked alone the data for what should be CB_1 shows up and nothing else. No other checkboxes show up when they are checked.

Also, if turn off the "ON error resume" line it comes up with the same error as earlier which is "Unable to get the Checkboxes property of the Worksheet class".

Suggestions?

[kiyiya]

XLD,

Looks like the problems I were having were not related to the code but related to the position of the checkboxes. To solve that problem, I change the row height to 15 and now the references work perfectly. As far as the loop breaking goes.......... I had not renamed all the chek boxes to CB_ so when the loop found one named check box 3 (for example) it stopped.

Thanks for your help! I still would like to know how to do the same thing based off of a column loop looking for all cells with a "TRUE" value, however.

[Bob Phillips]

I can't see how a column loop would work because the checkboxes are layered on a worksheet, they are not part of it.

You could use a different method using simulated checkboxes, but not with the real thing.

[kiyiya]

It really would not be looking at the checkbox just the cell that you have to associate with the checkbox. When you click the checkbox the cell link that you set up in the control will populate as "TRUE". I thought I could look at that and make the determination of whether to move the data to the Receipt sheet. Of course I am new to all of this VBA.......... but I like it!

[Bob Phillips]

But then you are still looping through the checkboxes, not the cells themselves.

[kiyiya]

I guess I just do not understand. The cell link associated with the check box just contains a value of "TRUE" or "False" as far as I can tell. For example, on my test file CB_1 has a cell link of A6 so when CB_1 is checked A6 shows "TRUE". I guess I can not wrap my mind around why we could not check each cell in column A for a value of "TRUE" and then do something. There must be something I am just overlooking but I need to understand the why behind it before I can grasp it.
I guess we are still looping through the check boxes (my goal) but from a different approach. Your solution does exactly what I wanted but the setup for it requires a lot of work and makes future adjustments harder to do.

[Bob Phillips]

I think I see what you are getting at now. What I was saying was that A6 does not know it is linked to CB_1. The checkbox does, but the cell doesn't. If you just look at the cell value that is fine.

[Bob Phillips]

This is the other way I referred to. Click in column A or F to select those items then run the CreateInvoice macro.

[kiyiya]

That solution is pretty cool but it would allow someone to inadvertently "check" items that are not chargeable which would show up on the Receipt. I can probably get enough from your code to come up with something.

Thanks for all your hard work! Your awesome!

[Bob Phillips]

So does a checkbox!

[kiyiya]

True, but I would never put a check box by something I was not going to charge for. There are lots of "Headings" or "Categories" that could (and would) end up on the Receipt with that method.

I like it though. I guess I could just lock the cells that I did not want anyone to check as a solution.

[Bob Phillips]

The way to do it is to set a named range to just those cells that you allow a checkmark, and test for that in the event code.