UserForm to find cell value and use in a macro

[NotBillGates]

Hi
To simplify my problem in A1 is text Code1, A2 Code2, A3 Code3 etc
I want to add a box (possibly a userform) to each cell A1, A2, A3
When the box is selected run a macro which uses the value in the cell and assigns it to a fixed cell elsewhere

So if select box over A1 the macro assigns Code1 to MyString e.g MyString = [A1].Text
Then assigns to B1(Its always B1) using something like Range("B1").Value = Mystring

So if select box over A2 the macro assigns Code2 to MyString e.g MyString = [A2].Text
Then assigns to B1 (Its always B1) using something like Range("B1").Value = Mystring

If I insert a new row between row2 and 3 so A3 becomes A4 I still want the box in A4 to use Code4

I can create a shape, assign a different macro with hardcoded Range ("B1").Value = "Code1"
But I have to create a separate macro for every cell (there are more than the three in the example) and update the hardcoded value.

So I think I am asking
What is the best way to do this
If userforms are the way to go how do I assign a userform to a specific cell, and will this update if rows and or columns are inserted.
If I am assigning each userform to a cell will I need a userform for every cell
How do I get the value of the cell the userform is attached to

Thanks in anticipation

Rhys

 

[rpaulson]

If I understand correctly then this is all the code you need. you only need one code.

Sub copy_me()
Dim r As Long
r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
[B1] = Cells(r, "A")
End Sub

you can assign this same code to as many shapes you want anywhere on the sheet.

the code will copy the cell value that is in column A of row the that top of the shape is on.

hth,

Ross

 

[NotBillGates]

Thanks Ross, does exactly what I want.