My problem is how to link a form button to a cell and get that information
into a macro.

I have written a small practice macro to perform some matrix multiplication
assuming I have pre-selected the answer cells. Next I managed to get the
macro to automatically use the cells below the current active cell for the
answer array. Now, what I really want to do is apply this macro by selecting
a button above each array. The button has now information on where it is in
the sheet. I would like to lock it in a cell, and be able to reference the 4
cell below it as a range in my macro.

Any ideas?

Assuming forms buttons


Dim rng As Range

Select Case Application.Caller

Case "Button 1": Set rng = Range("C5:C8")

Case "Button 2": Set rng = Range("D5:D8")

Case "Button 3": Set rng = Range("E5:E8")

Case "Button 4": Set rng = Range("F5:F8")
End Select

'then work on rng

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Paul Jolley" wrote in message
...
My problem is how to link a form button to a cell and get that information
into a macro.

I have written a small practice macro to perform some matrix
multiplication
assuming I have pre-selected the answer cells. Next I managed to get the
macro to automatically use the cells below the current active cell for the
answer array. Now, what I really want to do is apply this macro by
selecting
a button above each array. The button has now information on where it is
in
the sheet. I would like to lock it in a cell, and be able to reference the
4
cell below it as a range in my macro.

Any ideas?

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

"Paul Jolley" wrote:

My problem is how to link a form button to a cell and get that information
into a macro.

I have written a small practice macro to perform some matrix multiplication
assuming I have pre-selected the answer cells. Next I managed to get the
macro to automatically use the cells below the current active cell for the
answer array. Now, what I really want to do is apply this macro by selecting
a button above each array. The button has now information on where it is in
the sheet. I would like to lock it in a cell, and be able to reference the 4
cell below it as a range in my macro.

Any ideas?

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

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

thanks for the help Tom, works a treat! Now just need to tidy things up.


"Tom Ogilvy" wrote:

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