turning this vba code more dinamic

Beneindias

Board Regular
Joined
Jun 21, 2022
Messages
116
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello, excel gurus,

So, I have a vba code that checks every checkbox that i have in a table, and deletes said checkbox if the row is empty or outside of the table, but I had to put a "+8" in the row range, because the table starts at row 7 of my worksheet.
Problem with this, is if someone make any change in this sheet and had or delete a row in before the table, I think that this code will not work very well.

This is my code:

VBA Code:
   Dim PagoColumn As String: PagoColumn = "B"

    Set PagoColumn = "B"

    With Sh
        'Número da última linha da tabela
        lastRow = WorksheetFunction.countA(Range(LastRowColumn))
    End With

    For Each Cbx In ActiveSheet.CheckBoxes

        'Checa se o espaço não está ocupado por uma checkbox. "lastRow + 8" para que seja considerada a linha após o fim da tabela
        If Not Intersect(Cbx.TopLeftCell, ActiveSheet.Range(PagoColumn & lastRow + 8)) Is Nothing Then

            'Apaga a Checkbox, se esta estiver na linha após a tabela
            Cbx.Delete

        'No caso de não haver nenhuma checkbox após a tabela, este próximo código checa se a checkbox tem ligação a alguma célula
        '(quando a linha é apagada, a checkbox perde a ligação à célula e fica com erro #REF!)
        ElseIf Cbx.LinkedCell = "#REF!" Then

            'Apaga a checkbox se esta não tiver nenhuma célula ligada
            Cbx.Delete

        End If
    'Passa para a próxima checkbox
    Next Cbx

Can I make any change in this code, to turn it completely dinamic according to the table?

Thank you all
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Perhaps something along the lines of this
VBA Code:
    Dim tblRange As Range
    Dim cbx As CheckBox

Set tblRange = ActiveSheet.ListObjects("Table1").Range
    'MsgBox tblRange.Address(0, 0)   'just for varification

For Each cbx In ActiveSheet.CheckBoxes
    If Not Intersect(cbx.TopLeftCell, tblRange) Is Nothing Then
        ' this cbx is within the table
        MsgBox cbx.Name & " is within the table"
        ' your code here for this situation
    Else
        ' this cbx is not within the table
        MsgBox cbx.Name & " is not within the table"
        ' your code here for this situation
    End If
Next cbx
 
Upvote 0

Forum statistics

Threads
1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top