Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with using range names in sum function | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |