range moves with application caller
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 |
range moves with application caller
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 |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com