VBa to copy and paste data into a cell when a dropdown is selected

[tonywatsonhelp]

Hi Everyone,

Hopefully someone has come up with this problem before and know how to deal with it but i have an idea that should work.

Hers my problem,
I have a list of dropdown options in AC11:Ac100
in X20 i have a dropdown box linked to this list

Now the problem i have is this dropdown list is unusual as some of the cell contain formulas for example lets say D17 was a name the cell might read "Message for " & D17, to show that name
but when i select from the dropdown box i get "Message for TONY" (tony being the name in D17 at the time) but I want it to give me the same formula so X20 = "Message for " & D17,

So this is what i was thinking,

I need a macro that can copy the dropdown selected and paste it into X20

So if I select "Message for TONY" from the dropdown the macro is triggered goes find "Message for TONY" in AC11:Ac100 copies and pastes it in as a formula.

now any ideas how i can do this.
(Or any other way to get the formula in would do fine )


please help if you can

Thanks

Tony

 

[Micron]

Maybe this will get you started:

Sub NameOfDropDownHere_Change()
Dim dd As DropDown

Set dd = ActiveSheet.Shapes("NameOfDropDownHere").OLEFormat.Object
MsgBox ActiveSheet.Range("A" & dd.ListIndex).Formula

End Sub

Change A to whatever column contains your list values.