Userform Listbox Help: Without using Command Buttons

[coach_veto]

Hi,

So I recently made a userform listbox that references a list of countries I have. I have the following code pasted in Sheet1 so that when I click one of the cells referenced, the userform pops up.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
UserForm1.Show
End If
End Sub

My problem is that once the list pops up, I want to be able to select one of the country names from this list and have it populate that country name in the active cell. I want to do this without having to create and click a command button. I also would like the list to disappear once a selection has been made and the cell is populated.

What code would I need? Does it go in the sheet1 area or the userform code area?

Thanks guys!

 

[steve the fish]

In the userform module try this:

Private Sub ListBox1_Change()
ActiveCell.Value = ListBox1.Value
Unload Me
End Sub

 

[Eric W]

You can do all that without VBA using Data Validation. If you have your country list in D1:D5 for example,

1) Select your A1:A10 range
2) From the Data tab, click on Data Validation
3) In the Allow: box, select List
4) Make sure In-Cell dropdown is checked
5) In the Source box, enter
=$D$1:$D$5
(or whatever your range is)

And you're done.

If you want, you can just list the countries in the Source box without saving them on the sheet. Just put them in the Source box separated by commas.

Let me know if this works for you.

 

[Andrew Poulsom]

Try this:

'Worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            UserForm1.Show
        End If
    End If
End Sub

'UserForm module

Private Sub ListBox1_Click()
    ActiveCell.Value = ListBox1.Value
End Sub

 

[coach_veto]

Thank you guys, these solutions all work.

@EricW I chose not to use a simple drop down list from data validation because I need my column width to be small but the countries in this list have long names. So if used a normal drop down, the list would cut off the names unless I made the column bigger, which I don't want to do. Thanks again.