Get Active cell from a text box that has macro attached

[NotBillGates]

Hi
I am trying to update a cell A2 with a different value depending on which macro I run. Each macro is run from a text box placed over a different cell.
In each cell A1 to Z1 have a unique piece of text e.g. A1 = Reason1, B1 Reason 2 etc
I have a textbox over each cell which gets its text from the cell underneath e.g. text box over A1 has =A1, B1 =B1
I can attach a macro to each text box with the following

Textbox over A1
Sub Reason1
Range("A2").value = range ("A1").value
End Sub

Textbox over B1
Sub Reason2
Range("A2").value = range ("B1").value
End Sub

Textbox over Z1
Sub Reason2
Range("A2").value = range ("Z1").value
End Sub

Is there a way to use a single macro for all text boxes that links to the cell below the textbox. So when run from textbox over A1 Active cell is A1, when run from textbox over B1 Active cell is B1
Sub SingleMacro
Range("A2").Value = Activecell.Value
End Sub

Alternatively if I get rid of the text boxes how do I run a macro if cell A1, B1, C1...Z1 are selected, but not run if any other cell is selected.

Hope this makes sense
Thanks

 

[MickG]

Perhaps this:-
This code fills "A2" with any selected value from "A1 : Z1".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("A1:Z1")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    Range("A2").Value = Target.Value
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NotBillGates]

Hi Mick
Thanks for the reply.
I created a new workbook with values in A1 to Z1 of sheet1
I selected view code for sheet 1, and pasted in your code
However selecting any of the cells A1 to Z1 doesn't put a value in A2
Any suggestions as to what I am doing wrong.
Unfortunately I don't have permissions to attach the file.

Thanks
Rhys

 

[MickG]

You may be in "Design Mode"
If you click the "Developer" on the ribbon, then click "Design mode" the Icon colour will change from the background colour to light Orange, you need it to be Background colour.

 

[NotBillGates]

Hi Mick
I have checked and I am not in Design Mode

 

[MickG]

Perhaps you can see what's wrong from this example file:-
https://app.box.com/s/jydofa0vnnthpkuw9ll63by0ss9xu8gg

 

[NotBillGates]

Thanks Mick
No idea what's going on but your file didn't work after first download, but worked fine after second download. Opened my own file again and it works fine.
Does the ByVal Target As Range mean run this macro if a cell is selected, and the second line of code restrict to A1 to Z1
Once again thanks for the code.

 

[MickG]

Glad you have it working.
The code basically says if you select any value (target.value) in "A1 to Z1" (intersect with target and "A1:Z1") then the value of that selected cell will show in "A2".
Regrds Mick