Checkbox problems

[randomdrums]

Hi all, new forum member here so please be kind and let me know if ever I haven't posted in the right place or the right thing.

I am having problems with checkboxes in a very large Workbook that involves multiple sheets and what not, but I will try to be as precise as possible.

This particular worksheet is an Invoice register, which has a few columns and that automatically get added via another macro directly from my Invoice Template sheet. At the end of each row of data, I have setup a checkbox. The idea is that when the invoice is paid, the checkbox gets clicked and the infomation in that invoice's row get put into a table in another worksheet, making it easy to keep track of what has been paid or not, and then have the "Transactions" page calculate all my tax and income tax stuff (which already contains huge amounts of formulas and other information).
After writing it all out, I get a Run-Time Error 1004 on the couloured line and I'm not sure how to fix it. Any help would be appreciated.

Here is the VBA code for said Macro, and I will also include a screenshot of the sourceSheet and targetSheet as well. I will also note that each Checkbox is cell linked to the cell is it in/on, but the TRUE or FALSE statement is just hidden by making the text white.

Sub TransferDataToTransactions()

    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim nextFreeRow As Long
    Dim dataToCopy As Variant
    Dim i As Long
    Dim lastRow As Long
    Dim currentRow As Long
    Dim chkBox As CheckBox
    
    Set sourceSheet = ThisWorkbook.Sheets("Invoice Record") ' Change to your source sheet name
    Set targetSheet = ThisWorkbook.Sheets("Transactions") ' Change to your target sheet name
    
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row
    
    For i = 1 To lastRow
[COLOR=rgb(250, 197, 28)]        Set chkBox = sourceSheet.CheckBoxes("CheckBox" & i)[/COLOR]
        If chkBox.Value = 1 Then
        dataToCopy = Array(sourceSheet.Cells(i, "B").Value, sourceSheet.Cells(i, "C").Value, sourceSheet.Cells(i, "D").Value, sourceSheet.Cells(i, "E").Value)
            
            ' Find the next free row in the target sheet's table
            nextFreeRow = targetSheet.ListObjects("TransactionTable").ListRows.Count + 1 ' Change "TransactionTable" to your table name
            
            ' Copy data to the next free row in the target sheet
            targetSheet.ListObjects("TransactionTable").ListRows.Add
            targetSheet.Cells(nextFreeRow, 1).Value = Date
            targetSheet.Cells(nextFreeRow, 2).Value = dataToCopy(0)
            targetSheet.Cells(nextFreeRow, 3).Value = dataToCopy(1)
            targetSheet.Cells(nextFreeRow, 5).Value = dataToCopy(2)
            targetSheet.Cells(nextFreeRow, 6).Value = dataToCopy(3)
            
            ' Sort after each row is added
            ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
                SortFields.Clear
            ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable").Sort. _
                SortFields.Add2 Key:=Range("TransactionTable[[#All],[AAAA-MM-DD]]"), SortOn _
                :=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Transactions").ListObjects("TransactionTable"). _
                Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next i

End Sub

 

[kevin9999]

As far as the colored line goes, when you inserted the checkboxes on the sheet - did the names appear (in the name box) as "Checkbox1" or as "Check Box 1"? Or did you change the names to be exactly as you have "CheckBox" in your code (no spaces)?

 

[randomdrums]

I erased the name completely since I didn't need a text box for every CheckBox. Should I have not?

 

[randomdrums]

Update: I modified the Macro that add the data from my invoice to my Invoice Record sheet by having it add a Checkbox automatically at the end of each row in Column F, which automatically links to the Cell of that row and adds the Macro that I am trying to fix to the checkbox automatically as well. However, the Macro that needs fixing still doesn't work.

 

[kevin9999]

I wasn't meaning the text box, I meant the actual name from the name box (right click on the first check box and look at the name - top left of the screen). If I add a check box to a sheet & refer to it in code as "CheckBox1" - the code cannot find a check box of that name & will fail. But if I refer to it as "Check Box 1" then I don't have an issue.

 

[randomdrums]

So I just looked and the Checkbox does have a name. See inclosed screenshot. So now what do I do?

 

[kevin9999]

You see the problem, your code would be expecting "CheckBox7" on that row - a long way from what it's actually called. Normally I wouldn't refer to check boxes by their exact name in code for this very reason - you can soon lose track of what they're actually called. I prefer to loop through each checkbox on the sheet, along the lines of:

Dim cx as Excel.CheckBox
For Each cx In ActiveSheet.CheckBoxes
If cx.Value = xlOn Then

do your actions here if the check box is true

And to refer to the row the checkbox is on, you can use something like:

cx.TopLeftCell.Row

 

[MikeVol]

But in your code the name of the checkbox:

"CheckBox" & i

See Check Box 2157 difference or not? Also the checkbox number should have the same as the row number, like CheckBox7
.

 

[randomdrums]

Ok sorry guys, I'm more of a noob that I thought and I'm not following you here... a little more detail would be appreciated

 

[kevin9999]

I can't spend time on this now, but if you can provide an XL2BB - Excel Range to BBCode sample of your source & target sheets (you can disguise any sensitive data, and only a few records would be needed) then I could show you an alternative method of achieving your aim - just need to know if there's any other way of marking a record as "paid" other than the checkbox. It'll be tomorrow (my time) before I can look at it.