Set a cell value equal to a currently selected string

George C

New Member
Joined
Apr 30, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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


Acronyms.jpg


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
VBA Code:
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

New Member
Joined
Apr 30, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Yongle, thanks for your help, but I’m having difficulties implementing your solution.

I followed your instructions:

Inserted Listbox1 on the sheet:

1588347365785.png


Added your code to the Sheet code window:

1588347582669.png


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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Yes C2 was a typo!
I do not see anything that you are doing wrong :unsure:

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,473
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

George C

New Member
Joined
Apr 30, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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 ...
VBA Code:
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 ...
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
[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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
This link may also be of interest
- not directly wahat you want but you may be able to adapt it
 

Forum statistics

Threads
1,141,487
Messages
5,706,663
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top