TopLeftCell error with macro to set variables based on the button that is pressed

Question

I'm trying to write a sub that will delete the contents of a row and move the contents of everything below that row up to fill the gap.

I want to apply the same macro to each command button on the spreadsheet, at creation, so I wanted to write the macro such that it gets all its position information from the action of clicking the button that calls the macro.

For some reason, it just started giving me an "Unable to get the TopLeftCell property of the Button class" error (it used to work pretty okay, but somehow I broke it as I was trying to fix it).

What am I doing wrong?

Here is my code

Sub DelTask()

Dim btn As Object   'variable to be used as the button pressed
Dim btnadd As String    'variable for the address of the pressed button
Dim tsk As Range    'Variable for the location of the task name
Dim tskadd As String
Dim btnRow As Integer    'variable to hold the row of the button pressed
Dim delcell As Range
Dim shftcell As Range
Dim shftcells As String

NumTasks = Application.WorksheetFunction.CountA(Sheets("Tasks").Range("B7:B10000"))
Set btn = ActiveSheet.Buttons(Application.Caller)
    With btn.TopLeftCell
        btnadd = .Address
        btnRow = .Row
    End With
Set tsk = Sheets("Tasks").Range(btnadd).Offset(0, -11)
tskadd = Sheets("Tasks").Range(btnadd).Offset(0, -11).Address

Here is the code from the macro that creates the buttons in the first place:

Dim CaseyCell As Range      'Range Variable
Dim CellAdd As String       'variable to hold address of cell
Dim CaseyDel As Range       'Range variable for delete checkbox
Dim CellDel As String       'variable to hold address of delete checkbox
Dim btn As String

Set CaseyCell = Sheets("Tasks").Range("ToDo_Start").Offset(NumTasks + 1, 10)
Set CaseyDel = Sheets("Tasks").Range("ToDo_Start").Offset(NumTasks + 1, 11)
'CaseyCell.Select
CellAdd = CaseyCell.Address(False, False)
CellDel = CaseyDel.Address(False, False)

If New_Edit = "New" Then
    With ActiveSheet.CheckBoxes.Add(CaseyCell.Left, CaseyCell.Top, CaseyCell.Width, CaseyCell.Height)
'       .name = "Chkbx" & CellAdd
        .Caption = ""
        .LinkedCell = CellAdd
        .Placement = xlFreeFloating
        .OnAction = "CrossOutRow"
    End With

    With ActiveSheet.Buttons.Add(CaseyDel.Left, CaseyDel.Top, CaseyDel.Width, CaseyDel.Height)
        .Name = "Button" & CellDel
        .Caption = "DELETE TASK"
        .Characters.Font.Name = "Calibri"
        .Characters.Font.Size = 12
        .Characters.Font.StrikeThrough = False
        .Characters.Font.Superscript = False
        .Characters.Font.Subscript = False
        .Characters.Font.Shadow = False
        .Characters.Font.Underline = xlUnderlineStyleNone
        .Characters.Font.ColorIndex = xlAutomatic
        .OnAction = "DelTask"
    End With
End If

“'m trying to write a sub that will delete the contents of a row and move the contents of everything below that row up to fill the gap.“ the delete function will do this btw `Rows(i).EntireRow.Delete shift:=xlUp` — Marcucciboy2, Aug 06 '18 at 01:44
Alternatively `Range(“A:B”).Rows(i).Delete shift:=xlUp` to just delete a row for a small section — Marcucciboy2, Aug 06 '18 at 01:50
None of this addresses my question. My question is what is wrong with my code that the click on the command button isn't able to pull a TopLeftCell reference? I can't delete the row, because then all the checkbox linked cells don't function. — Casey, Aug 06 '18 at 02:00

score 0 · Answer 1 · answered Aug 06 '18 at 02:40

You need to set btnadd

NumTasks = Application.WorksheetFunction.CountA(Sheets("Tasks").Range("B7:B10000"))
Set btn = ActiveSheet.Buttons(Application.Caller)
Set btnadd = btn.topleftcell
btnRow = btn.topleftcell.Row

Set tsk = Sheets("Tasks").Range(btnadd).Offset(0, -11)
tskadd = Sheets("Tasks").Range(btnadd).Offset(0, -11).Address

You'll have the same issue with tsadd if you used it the same way I think. Topleftcell returns a range object, you dont need to get its address to use it in range(name).

[Edit: changing the code to set btnadd as you suggested instantly broke the code again. I returned it to the way it was, minus the ".name" line.] Ooh! Good to know! Thank you, so much! — Casey, Aug 06 '18 at 03:35

score 0 · Answer 2 · edited Aug 06 '18 at 03:37

0

My error was in naming the command buttons with their destination cells. Each time a command box was created after any cells had been shifted, it would have a duplicate name, and the .TopLeftCell property wouldn't work.

I don't exactly know why, but removing the .name = "Button" & CellDel line from my code seemed to fix it.

Thanks for the suggestions!

edited Aug 06 '18 at 03:37

chris neilsen

52,446
10
84
123

answered Aug 06 '18 at 03:21

Casey

65
8

1

Removing `.name = "Button" & CellDel` leaves the button name at its default value, which is unique. Since you are not otherwise using the name, this has very little downside. If you _did_ want to rename the buttons, just ensure you give them unique names. – chris neilsen Aug 06 '18 at 03:40

TopLeftCell error with macro to set variables based on the button that is pressed

2 Answers2