Condition to check if there are checkboxes

[Beneindias]

Hello, Excel Wizards,

I have an excel file where I have some VBA code to create a checkbox in column B, everytime I add a row to a table.
But this code is not finished and I've been trying to improve it, but my knowledge in VBA is really limited.

So, my code is creating the checkboxes in column B and linking them to column C.
But I had to hard code the rows, because I think that the code was getting the number of the row, as the number of the row inside the table, but then, it was creating the checkbox in the same number, but considering the worksheet row, so I add to make it like "lastRow + 7", because my table starts in row number 7.
So, if someone can help me change that, so that it considers table row when creating checkbox, it would be awesome.

Next thing;
As I had the code, it was creating a checkbox in the last row everytime i made changes to the worksheet.
I tried to make a condition to check if in column B of the lastRow there was a checkbox, but it's not working and it's giving me an error, now: "Run-time error '438': Object doesn't support this property or method"
The error is in the line where I have my IF condition: " If Not Application.Intersect(Cells(lastRow, PagoColumn), ActiveSheet.CheckBoxes.TopLeftCell) Is Nothing Then"

Sub Checkboxes_Creation()
Dim lastRow As Long
Dim sh As Worksheet
Dim worksheet1 As String: worksheet1 = "Salarios" 'Salarios
Dim PagoColumn As String: PagoColumn = "B"
Dim StatusColumn As String: StatusColumn = "C"
Dim LastRowColumn As String: LastRowColumn = "l:l" 'Include Entire Column.
 
    Set sh = Sheets(worksheet1)
 
    With sh
        lastRow = WorksheetFunction.CountA(Range(LastRowColumn))
    End With
    If Not Application.Intersect(Cells(lastRow, PagoColumn), ActiveSheet.CheckBoxes.TopLeftCell) Is Nothing Then
        With sh.Range(PagoColumn & lastRow)
            With sh.CheckBoxes.Add(Cells(lastRow, PagoColumn).Left, Cells(lastRow + 7, PagoColumn).Top, 10, 10)
                .Caption = ""
                .Locked = False
                .LockedText = False
                .Value = xlOff
                .LinkedCell = Cells(lastRow + 7, StatusColumn).Address
            End With
            Call CenterCheckbox
        End With
    End If
End Sub

Can anybody help me? The thing I realy need, is to check if there is a checkbox in my lastRow in column B. If a checkbox exists there, do nothing, if not, it needs to create that checkbox.

As a bonus, I would like to swap "lastRow + 7" for something that was more "dynamic" and looked for lastRow INSIDE THE TABLE.

Thanks for enyone that can help me.

 

[Domenic]

When you say table, do you mean one created with Ribbon >> Insert tab >> Tables group >> Table ?

To check whether a checkbox exists at a specific location, use the following function that returns True if one exists and False if it doesn't...

Public Function CheckBoxExists(ByVal target As Range) As Boolean

    Dim ws As Worksheet
    Set ws = target.Parent
    
    Dim chbx As CheckBox
    For Each chbx In ws.CheckBoxes
        If Not Intersect(chbx.TopLeftCell, target) Is Nothing Then
            CheckBoxExists = True
            Exit Function
        End If
    Next chbx
    
    CheckBoxExists = False

End Function

You can test whether a checkbox exists like this...

    If Not CheckBoxExists(sh.Range(PagoColumn & lastRow)) Then
        'Checkbox does not exist
        'create CheckBox
        'etc
        '
        '
    End If

Hope this helps!

 

[Beneindias]

When you say table, do you mean one created with Ribbon >> Insert tab >> Tables group >> Table ?

To check whether a checkbox exists at a specific location, use the following function that returns True if one exists and False if it doesn't...

Public Function CheckBoxExists(ByVal target As Range) As Boolean

    Dim ws As Worksheet
    Set ws = target.Parent
   
    Dim chbx As CheckBox
    For Each chbx In ws.CheckBoxes
        If Not Intersect(chbx.TopLeftCell, target) Is Nothing Then
            CheckBoxExists = True
            Exit Function
        End If
    Next chbx
   
    CheckBoxExists = False

End Function

You can test whether a checkbox exists like this...

    If Not CheckBoxExists(sh.Range(PagoColumn & lastRow)) Then
        'Checkbox does not exist
        'create CheckBox
        'etc
        '
        '
    End If

Hope this helps!

This worked.
Thanks

 

[Domenic]

You're very welcome, cheers!

 

[Beneindias]

And the code does not work anymore.

Every time I run this code, nothing happens.
I made few changes in my file, but nothing that changed structure or anything like that, just deleting a few columns and changing fomating.

How can I see what's happening "behind the cenes" when I run a macro?

Cant understand why the code stoped working. I simply deleted a checkbox aind tried to run the code to create a new checkbox on the same row. (something I have done multiple times already).

Can anybody help me?

 

[Beneindias]

And the code does not work anymore.

Every time I run this code, nothing happens.
I made few changes in my file, but nothing that changed structure or anything like that, just deleting a few columns and changing fomating.

How can I see what's happening "behind the cenes" when I run a macro?

Cant understand why the code stoped working. I simply deleted a checkbox aind tried to run the code to create a new checkbox on the same row. (something I have done multiple times already).

Can anybody help me?

Ok. I had hidden column A, and it ****ed the code.
Now have column A with 0,1 width, and it works again.

 

[Domenic]

Okay, that's great, glad you've sorted it out.

Cheers!