Open list when cell in column gets focus, and append term in list to text

[davidmyers]

Hi, I have columns that contain text, each column has a list of terms that are related to the text in the cells of that column.
What I would like/need your help to do is:
1. When a cell in one of the columns gets focus a list of the relevant terms for that column will open - and remain open till a cell in another column gets focus.
2. When the user enters text in the cell he can click a term in the list and it will be appended to the text.

Any vba snippets or relevant links much appreciated
Thanks
David

 

[Roderick_E]

Sounds like using a userform with vbmodeless to popup the list. The user then can select from a listbox on the form and do as you desire.

 

[davidmyers]

Thanks Roderick, My first ray of hope, going to give it a go, let you know.

David

 

[davidmyers]

Ok! this opens up the listbox and I can continue entering text into the cell with the listbox open, now I'm trying to click an item in the list box and get it inserted at the cursor I've tried this:

Dim i As Long, r As Long, c As Long
r = ActiveCell.Row
c = ActiveCell.Column
With Me.ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) = True Then Selection.InsertAfter .Column(r, c)
    Application.ScreenRefresh
    Exit Sub
  Next
End With

but nothing happens, any ideas?

Thanks David