Adding Checkboxes next to pivot table that are added until the last row of information in the pivot table

Question

Dear all: I need to add check boxes to column A, that is to the left of my pivot table, starting form column B to column D. I also want that the check boxes are linked to the extreme right of the pivot table, that is to say column E.

I have found a code that does that. However it is limited to a specific range ("A4:A9"). I wanted that the code was able to add check boxes regardless of the length of the pivot table dynamically in case it grows in other words to the last row.

Please find attached the whole code that I have already

Sub AddCheckBox()
    Dim cell As Range

    DelCheckBox  'Do the delete macro
    'or delete all checkboxes in the worksheet
    ' ActiveSheet.CheckBoxes.Delete
    Dim MyRow As Long

    lastrow = Cells.Find("*", Range("A1"), xlValues, , xlByRows, xlPrevious).Row

    For Each cell In Range("A4:A9")
        With ActiveSheet.CheckBoxes.Add(cell.Left, _
            cell.Top, cell.Width, cell.Height)
            .LinkedCell = cell.Offset(, 4).Address(External:=True)
            .Interior.ColorIndex = 37   'or  xlNone or xlAutomatic
            .Caption = ""
            '.Border.Weight = xlThin
        End With
    Next

    With Range("A4:A9")
        .Rows.RowHeight = 15
    End With
End Sub

Sub DelCheckBox()
    For Each cell In Range("A4:A9")
        Worksheets("Analysis").CheckBoxes.Delete
    Next
End Sub

I have found a code that identifies the last row. However, I must be doing something wrong because It doesn't seem to work when I try to insert it with the rest of the code. In fact, I don't know where I need to insert it for it to work properly. Could anyone help me please to identify what I need to do?

Many thanks in advance for all your help.

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

Try using lastrow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).end(xlUp).Row to find the last row in the column instead of your method. Also change Range("A4:A9") to RangE("A4:A" & lastrow) anywhere that it is referenced in your code (Also change the 4 to the first row of the used cells in column A)

Source

Update after comments below

I've modified your code above. Yours wasn't working as you were deleting the checkboxes before 'counting' them. Hence it was only adding them from rows 1 to 4. If you change the NoRow column E to a constant column in your sheet this will do what you want it to.

Sub AddCheckBox()
    Dim cell
    Dim NoRow As Integer: Dim firstRow As Integer
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Analysis")
    With ws
        .CheckBoxes.Delete
        ' Change the `E` to the column that the checkboxes are aligning with
        NoRow = .Range("E" & .Rows.Count).End(xlUp).Row
    End With

    For Each cell In Range("A4:A" & NoRow)
        With ws.CheckBoxes.Add(cell.Left, _
            cell.Top, cell.Width, cell.Height)
            .LinkedCell = cell.Offset(, 4).Address(External:=True)
            .Interior.ColorIndex = 37   'or  xlNone or xlAutomatic
            .Caption = ""
            '.Border.Weight = xlThin
        End With
    Next

    With ws.Range("A4:A" & NoRow).Cells
        .Rows.RowHeight = 15
    End With
End Sub

edited May 23 '17 at 10:26

Community

1
1

answered Jun 19 '15 at 09:24

Tom

9,725
3
31
48

Thanks a lot for your quick reply :) However when I did the modifications and tried to run the code it highlighted a "Compiled error" for lastrow = Activesheet.Range("A" & .Rows.Count).End(xlUp).Row saying that is invalid or unqualified reference. I think for the ".Rows". Do you know how to fix that? Many thanks – Gabriel Jun 19 '15 at 09:51
My bad - copy and paste error. `lastrow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).end(xlUp).Row` – Tom Jun 19 '15 at 09:53
Don't worry hehe. I have tried the code that you suggested – Gabriel Jun 19 '15 at 10:07
I have made the modifications that you mentioned but it seems that is only adding checkboxes from A1 to A4 only. Do you know why? Also for the Delete Checkboxes function it seems to highlight an error when I run the macro. Do you have any thoughts on this? – Gabriel Jun 19 '15 at 10:11
Lets got back to the beginning - I get the feeling you're fairly new to vba. What are you trying to align the checkboxes with? Column `E`? – Tom Jun 19 '15 at 10:15
Thank you very much for your code and all your help. It has proved to be really helpful. Yes, i am fairly new to VBA but little by little we are getting there. :) – Gabriel Jun 22 '15 at 08:12

Adding Checkboxes next to pivot table that are added until the last row of information in the pivot table

1 Answers1