User form question

[Gareth]

I have an user form with a list box on it. The source of the list box is a
range of cells on sheet1 (A1:A30), the cells contain quite long sentences.

What I would like to do, if possible, is to be able to click an item in the
list, then click the OK button and the text selected would be put into the
active cell on the activesheet.

Any help gratefully received.

Gareth

 

[Ron de Bruin]

You can use this event in the userform module

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = Me.ListBox1.Value
End Sub

If you DblClick the value will be in the active cell


Or the single click event

Private Sub ListBox1_Click()
' your code
End Sub

 

[Chip Pearson]

Gareth,

Use the following code in your form's code module. Change "ListBox1" to the
name of your list box and "CommandButton2" to the name of your OK button.

Private Sub CommandButton2_Click()
With Me.ListBox1
If .ListIndex >= 0 Then
ActiveCell.Value = .List(.ListIndex)
End If
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com