range moves with application caller

[ME @ Home]

Sub lined2()

'get the cell refrence of the button which activated the macro
Dim sAddress As String
sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address(0,
0)

'get the default reg number and check if valid

Dim reg As String
Range("a2").Select
reg = InputBox("Default Registration Number is " & Chr$(13) & Chr$(13) &
ActiveCell.Value, "Reg", ActiveCell.Value)

using the above macro i need a way to get the a2 in range command to change
rows depending upon which button activates the macro.... in the example if
button on d2 is selected then the data is read from cell a2
however if cell d7 is selected i need it to read from cell d7 not a2 as it
is currently doing , is their a way to make the row number move dependant
upon which button is pressed,
since the sheet as 3 rows of buttons it would be great if it could also read
from say cell f5 if the button in i5 was pressed ????

in essence the data i need it to read is always 3 cells to the left of the
button being pressed,

and data is then pasted into a cell imediatly to the left of the cell being
pressed is this possible

[Kevin B]

You can add the cell address as an argument for the sub and then provide the
argument value when each button calls the routine.

So if you changed the sub to lined2(ButtonAddress as string) you could
assign the button argument to the command buttons Click event. The code
below uses CommandButton1 and CommandButton2 as the button names.

Private Sub CommandButton1_Click()

lined2 "D2"
End Sub

Private Sub CommandButton2_Click()

lined2 "D7"

End Sub

--
Kevin Backmann


"ME @ Home" wrote:

> Sub lined2()
>
> 'get the cell refrence of the button which activated the macro
> Dim sAddress As String
> sAddress = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address(0,
> 0)
>
> 'get the default reg number and check if valid
>
> Dim reg As String
> Range("a2").Select
> reg = InputBox("Default Registration Number is " & Chr$(13) & Chr$(13) &
> ActiveCell.Value, "Reg", ActiveCell.Value)
>
> using the above macro i need a way to get the a2 in range command to change
> rows depending upon which button activates the macro.... in the example if
> button on d2 is selected then the data is read from cell a2
> however if cell d7 is selected i need it to read from cell d7 not a2 as it
> is currently doing , is their a way to make the row number move dependant
> upon which button is pressed,
> since the sheet as 3 rows of buttons it would be great if it could also read
> from say cell f5 if the button in i5 was pressed ????
>
> in essence the data i need it to read is always 3 cells to the left of the
> button being pressed,
>
> and data is then pasted into a cell imediatly to the left of the cell being
> pressed is this possible
>
>