Copy a single selected word in a cell into memory using vba /or sendkeys

[BobTheSpreadsheetBuilder]

Hi, I have a simple task to do but my attempts seem to just result in pasting a line of vba into the destination cell, instead of the value selected.

You can see in the attached image that I have a button to click on (with attached subroutine), a string of words in one cell, only one of which is selected, and a destination cell.
Now, I can press CTRL+C with my left hand before clicking the button (with a paste script) to achieve this.
However as I expect to do this many times, I wish for a script that will accomplish the CTRL+C part and allow me to paste the selected word in one click.

I have tried using SendKeys but its not working correctly. As you can see in this example, instead of pasting "primary", it pasted a vba code line "Application.SendKeys"

Does anyone have any scripting knowledge that can make this work ? Any advice is greatly appreciated

Bob

 

[jolivanes]

If you select a cell with the text of which you want a specific word copied, you could do this if it suits you.
Have a button on the sheet with following code assigned to it:

Sub Lets_Do_It()
UserForm1.ListBox1.List = Split(ActiveCell.Value, " ")
UserForm1.Show
End Sub

Have a UserForm named "UserForm1" with a ListBox named "ListBox1" (Both names without double quotes)
In the UserForm Module, put this code. Change reference (Offset(, 2)) as required.

Private Sub ListBox1_Click()
ActiveCell.Offset(, 2).Value = ListBox1
Unload Me
End Sub

When a cell with the text is selected, click on the macro button and select the word that needs to be copied from the ListBox.

 

[BobTheSpreadsheetBuilder]

Thank you for that solution jolivanes It works almost perfectly. I am using it where there are cells with less than 20 words to choose from



BoB

 

[jolivanes]

Thanks for the update.