Set a cell value equal to a currently selected string

[George C]

I'm trying to set up a feature in my workbook where a user can select an acronym anywhere on a spreadsheet and cell A1 will display the acronym selected. I'm planning to use the acronym appearing in A1 to display the spelled-out name in B1 via a lookup table.

For example, I'd like to set A1 equal to a currently selected text string. In this example, cell A2 has this text string in it: The CEO of ABC is John Smith.

I want to set A1 equal to any text string that I select. I click on A2 (making it the active cell) and select the acronym "CEO". I'd like A1 to instantly change its value to "CEO". I'll use CEO in a vlookup to set B1 equal to "Chief Executive Officer". Next, I select the acronym "ABC" in A2. I want A1 to instantly show a value of "ABC" (and B1 will instantly change to "American Broadcasting Corporation". And so on for any text string that I select anywhere on the spreadsheet.

I'm not trying to set A1 equal to the active cell's value -- this question was already answered in a thread back around 2006. I know that I can copy and paste the selected string into A1, but that involves extra steps. I don't want to use Smart Lookup (unless there is a way to customize Smart Lookup to just use my lookup table and not search the entire Internet). I just want to dynamically set A1 equal to the string that I have currently selected.

I'd appreciate any help on this.

 

[Yongle]

This provides a starting point for you
Right clicking on cell places the text from the cell in a listbox
Clicking on one of the words in the listbox places that word or acronym in cell A1
You may prefer to make the listbox visible/hidden at different times etc

To test, do the following
Insert an active-x ListBox on the worksheet
(code below assumes that the new listbox is listbox1 - amend if listbox is not Listbox1)
Add code below to the SHEET code window
Right-click on any cell containing text
Click on any of the words in the listbox


This code MUST be placed in the sheet code window (does not work in a module like Module1)
Right click on SHEET tab \ select view code \ paste code into the code window

Private Sub ListBox1_Click()
    Dim x As Long
    For x = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(x) = True Then
            Range("A1") = ListBox1.List(x)
            Exit For
        End If
   Next x
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With ListBox1
        .Top = Target.Offset(1).Top
        .List = Split(Target.Text)
        .Select
    End With
End Sub

 

[George C]

Yongle, thanks for your help, but I’m having difficulties implementing your solution.

I followed your instructions:

Inserted Listbox1 on the sheet:

Added your code to the Sheet code window:

Right clicked on C2 – nothing appeared in the listbox.
Assuming that C2 was a typo, also tried right clicking on A2 – nothing appeared in the listbox.

Can you tell me what I did wrong?

 

[Yongle]

Yes C2 was a typo!
I do not see anything that you are doing wrong

Perhaps events are not being triggered
Save, close and reopen the workbook - that's a simple way to reset everything

If still not working ... try putting a simple message box as the first line of both subs to see if they are being triggered
MsgBox "ListBox"
MsgBox "RightClick"

Let me know how you get on

 

[Fluff]

Also make sure you are not still in "Design Mode"

 

[Yongle]

@Fluff
- of course!

 

[George C]

Yongle and Fluff, I did as you instructed – thank you, it works. However, I now noticed that I have lost all other functionality of right clicking on a cell. For example, if I want to right click on a cell and choose Copy, then right click on another cell and choose Paste, I cannot do that. If I have to toggle on and off this new code whenever I want to use it, it probably won’t save me time or clicks versus copying and pasting the acronym into cell A1.

If I’m assigning the value of an active cell to A1, here’s what I am using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[A1] = ActiveCell
End Sub

Excel knows the current value of the active cell, so it knows that’s what goes into A1.

By similar reasoning, if I want to Copy and Paste a string somewhere, that string has to be cached somewhere. I’m looking for a formula or code that would allow me to do something like:

[A1] = CurrentCache

If a formula or code could work this way, I could avoid having to use the Listbox technique, and thereby retain all other right-click functionality.

Do you know of any formula or code that would could pull the contents of the cache in my example?

 

[Yongle]

Be patient and work with us and you will get something that works for you

Right-click is one example ...

you may prefer double-click ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    With ListBox1
        .Top = Target.Offset(1).Top
        .List = Split(Target.Text)
        .Select
    End With
End Sub

or double-click ONLY in columns A:C ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A:C")) Is Nothing Then
        Cancel = True
        With ListBox1
            .Top = Target.Offset(1).Top
            .List = Split(Target.Text)
            .Select
        End With
    End If
End Sub

etc ..

 

[Yongle]

[A1] = CurrentCache

If a formula or code could work this way, I could avoid having to use the Listbox technique, and thereby retain all other right-click functionality.

How do you propose selecting a specific acronym ?
- listbox is much cleaner than manually trying to select a word

Do you know of any formula or code that would could pull the contents of the cache in my example?

This link may be helpful

Windows Clipboard

www.cpearson.com

 

[Yongle]

This link may also be of interest
- not directly wahat you want but you may be able to adapt it

vba code for cursor position within a cell

Hi, Is there any vba code that would allow me to click into a cell, select a position within that cell and then run a macro to cut everything to the right of the cursor position and paste it into the next cell? I've tried to record a macro with the manual steps but the code doesn't show any...

www.mrexcel.com