Sub Btn_click()
Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
MsgBox btn.BottomRightCell.Address
btn.BottomRightCell.Offset(1, 0).Resize(4, 1) _
.FormulaArray = "=MMULT(translation_matrix,R[-6]C:R[-3]C)"
End Sub

worked for me.

--
regards,
Tom Ogilvy


"Paul Jolley" wrote:

You are right in that I am trying to use the button as a reference. I
implemented the routine you suggested bellow and do indeed get the message
box with $N$6.

I'm having problems using this value. I need to either set a range to this
value plus 4 cells below OR move the active cell there and I can carry on as
normal.

I tried both, but keep getting errors. It's probably because I don't know
how to use the info given by btn.BottomRightCell.Address

I tried the following:

Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
MsgBox btn.BottomRightCell.Address

Range(btn.BottomRightCell.Offset(1, 0).Resize(4, 1)).Select

Selection.FormulaArray = "=MMULT(translation_matrix,R[-6]C:R[-3]C)"

with problems in the range command. Any ideas?

thanks for the help to date.

"Tom Ogilvy" wrote:

Here are additional approaches:

if the button is from the control toolbox toolbar

set rng = activesheet.OleObjects("Commandbutton1").BottomRig htCell
rng.offset(1,0).Resize(4,1)

Demo'd from the immediate window with a commandbutton with the lower right
corner in cell F8
set rng = activesheet.OleObjects("Commandbutton1").Bottomrig htCell
? rng.offset(1,0).Resize(4,1).Address
$F$9:$F$12

For a button from the forms toolbar
set rng1 = Activesheet.Buttons("Button 2").BottomRightCell
rng1.Offset(1,0).Resize(4,1)

Demo'd from the immediate window
For a button from the forms toolbar located with the lower right corner in G8
set rng1 = activesheet.buttons("Button 2").BottomRightCell
? rng1.offset(1,0).Resize(4,1).Address
$G$9:$G$12

You can also look at the TopLeftCell property of each.

It sounded like you are just using these as reference points, but if you
attach macros to the forms buttons, you can use a single macro and get the
button name of the button that called it with Application.Caller

Dim btn as Button
set btn = Activesheet.Buttons(Application.Caller)
msgbox btn.BottomRightCell.Address

--
Regards,
Tom Ogilvy