Selection.InsertAfter Listbox1.Value not working

[mdavid]

Hi, Really appreciate any help on this

I have a userform with a single select list box that opens when I click in a cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim ws As Worksheet:    Set ws = ThisWorkbook.Worksheets("Complaints")
    Dim myCell As Range:    Set myCell = ws.Range("F2:F1321")
    Dim iSect As Range

    Set iSect = Application.Intersect(Target, myCell)

    If iSect Is Nothing Then Exit Sub
    UserForm4.Show vbModeless
End Sub

ShowModal: False

In ListBox1_Click() event what do I need to write, I thought

 Selection.InsertAfter Listbox1

would do it, but excel hangs after I make a selection on the listbox, I can't continue until I click Esc, but nothing actually gets copied.

Really need help with this

Thanks
David

 

[NARAYANK991]

Hi ,

Try this :

Private Sub ListBox1_Click()
            ActiveCell.Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
End Sub

Narayan

 

[mdavid]

Thanks for this Narayan,
What I want to do is manually enter text into a cell and at the same time select terms from the listbox that would be added at the cursor point, so whenever I select from the list I need:

PrivateSub ListBox1_Click()
            ActiveCell.Value = ActiveCell.Value & " " & Me.ListBox1.Value         
End Sub

but I now understand that if I'm in edit mode in a cell I can't just select from the listbox and stay in the cell - do you have a solution for this?

 

[NARAYANK991]

Hi ,

I am not able to understand your requirement ; can you explain clearly with an example if possible ?

Upload your workbook with data and form and code in it , so that we do not have to do it.

Narayan

 

[mdavid]

Hi Narayan, I've uploaded the file. What I want to do is both manually enter text into column F and also select terms from the list box which will be appended at the cursor, so for example I select "Headaches" from the listbox and then add manually in the cell " and ", then select "Migraines" from the listbox, then add manually ", also effects eyesight" so the cell text would be
"Headaches and Migraines, also effects eyesight".

I thought making the UserForm Modeless would make this possible, but I think I misunderstood.

Thanks for your help
David

 

[NARAYANK991]

Hi ,

You can at present select Headache and Migraines from the listbox , so that it appears in the cell as :

HeadacheMigraines

Then you can edit within the cell to change it to :

Headache and Migraines also affect eyesight

You cannot enter EDIT mode within the cell , and then make any selection within the listbox.

Narayan

 

[mdavid]

Thanks Narayan,
I've decided to add a text box under the listbox and copy the cell content to and from the textbox.

Thanks for you help
David