Runnig a macro relative from a form control

[Guest]

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?

 

[Bob Phillips]

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("D58")

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)

 

[Guest]

Here are additional approaches:

if the button is from the control toolbox toolbar

set rng = activesheet.OleObjects("Commandbutton1").BottomRightCell
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").BottomrightCell
? 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

 

[Guest]

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.

 

[Guest]

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

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.

 

[Guest]

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

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

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.

Here are additional approaches:

if the button is from the control toolbox toolbar

set rng = activesheet.OleObjects("Commandbutton1").BottomRightCell
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").BottomrightCell
? 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