Locate macro button

[tasander]

Hi

I want to create a spreadsheet where i can have a macro button embeded in a cell and when the user clicks the button, the cell to the right of the macro button displays some text.

The problem i have is that the button can be placed anywhere on the spreadsheet so i can not hard code the macro to select the correct cell.

I have found that if i make the macro button the exact same size as the cell then when the sheet is changed the button will change with it, this is an okay solution but an ideal one would be to embed a button within a cell.

Can anyone help with the abover or advise a better way to do this?

Many thanks

 

[RoryA]

Activesheet.Shapes(Application.Caller).TopLeftCell.Offset(0,1).Value = "blah"

for example.

 

[tasander]

Brilliant, thanks

Do you have any suggestions for embeding the cell or am i best just making the button the same size as the cell?

 

[RoryA]

You cannot actually embed a button in a cell, so all you can do is fit it to the same size.

 

[tasander]

okay thanks