Offsetting from a Shape

[rhudgins]

I would like to create multiple buttons on a worksheet that will run the following macro. At the beginning of the code instead of offsetting from the active cell is there a way to offset one row lower from the activeshape? Each shape will be in row 57 and I would like to offset to row 58 in the first line of the code.

Sub DeleteTrade()

ActiveCell.Offset(1, 0).Select

Range(ActiveCell, ActiveCell.Offset(35, 23)).Select

ActiveCell.Offset(36, 13).Select

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Select
    
ActiveCell.Offset(0, -14).Select

Range(ActiveCell, ActiveCell.Offset(0, 24)).EntireColumn.Select

Selection.Delete Shift:=xlToLeft

End Sub

[tigeravatar]

rhudgins

something like this?

ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(1, 0).Select

Hope that helps,
~tigeravatar

[rhudgins]

I am recieving an error that it is unable to get the buttons property of the worksheet class.

Will this execute only on the button that is clicked to run the macro?

[tigeravatar]

rhudgins,

The macro is designed to run from any button mapped to the macro. The Application.Caller method gets the button that was clicked (the item that "called" the macro).

~tigeravatar

[rhudgins]

Thank you this is helpful