Creating a drop down window

[billabong]

Hi
I would like some help in creating a drop down window were the user
selects a name and an address appears in certain cells. I have tried
creating a userform and adding a listbox but with no luck.

Are there any examples out there similar to this with VB code

Thanks

 

[Bob Kilmer]

Here is a simple example using a userform, a combobox and a command button.
You can leave the form open while you work. It inserts an address in the
selected cell when the user click the Insert command button.

'-------------------------------
'standard module
Option Explicit

Sub Main()
UserForm1.Show vbModeless
End Sub

'-----------------------------
'userform module
'with button "cmdInsert"
'and combobox ComboBox1

Option Explicit
Dim col As Collection

Private Sub cmdInsert_Click()
On Error Resume Next
With ComboBox1
ActiveCell.Value = col(.List(.ListIndex))
End With
End Sub

Private Sub UserForm_Initialize()

With ComboBox1
.AddItem "Larry"
.AddItem "Curly"
.AddItem "Moe"
.ListIndex = 0
End With

'Note the names in the list box are used as indexes to the collection
'so that a selected name identifies an address directly.
Set col = New Collection
col.Add "123 Fourth Street, Casper, WY 12345", "Larry"
col.Add "456 Seventh Avenue, Dayton, OH 54321", "Curly"
col.Add "432 First Street, Boise, ID 12321", "Moe"

End Sub

Private Sub UserForm_Terminate()
Set col = Nothing
End Sub

Bob Kilmer

 

[Bob Kilmer]

Hi
I would like some help in creating a drop down window were the user
selects a name and an address appears in certain cells. I have tried
creating a userform and adding a listbox but with no luck.

Are there any examples out there similar to this with VB code

Thanks

The following assumes you have a list of names in a worksheet column and an
address associated with each name in an adjacent cell to the right. It
further assumes that you have used the names to name the address cells. You
can do so by selecting the names and adjacent cells, then selecting Insert >
Name > Create > Left Column, OK.

Place a Listbox (named by default, ListBox1) and a CommandButton (name it
cmdInsert) on a user form and place the following code in modules indicated
(Main in a standard module; the other code in the userform module). Set the
RowSource in the ListBox Properties dialog to encompass the range of names
(but not addresses). Run Main. You can then select a cell in a worksheet,
choose a name in the listbox and click Insert or double click a name in the
list box to insert the associated address. In practice, and with a little
more refined code, the address worksheet or workbook could be hidden and
could be independent of the workbook where addresses are to be inserted.
This code will not insert data from more than one cell (e.g, an address
spread across a number of columns) but could be modified to do so rather
easily.

Note: Worksheet Named Range names cannot include embedded spaces. Excel will
insert an underscore in place of embedded spaces used to create named
ranges. For the above solution to work, the names that appear in the listbox
must be the same as the names of the Named Ranges ceated in Excel. This may
require the listed names to include underscores in place of spaces or that
you use "one-word" names. Less restrictive if somewhat more complicated
solutions are certainly possible that do not have this limitation.

'-------------------------------
'standard module
Option Explicit

Sub Main()
UserForm1.Show vbModeless
End Sub

'--------------------------------
'userform module
'ListBox1
'cmdInsert
Option Explicit

Private Sub cmdInsert_Click()
On Error Resume Next
With ListBox1
ActiveCell.Value = Range(.List(.ListIndex)).Value
End With
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
With ListBox1
ActiveCell.Value = Range(.List(.ListIndex)).Value
End With
End Sub

HTH,
Bob Kilmer