Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a drop down window
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a drop down window
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 <billabong wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a drop down window
<billabong wrote in message
... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating drop downs in a cell contingent on another drop down | Excel Discussion (Misc queries) | |||
Creating a pop up window | Excel Worksheet Functions | |||
Creating a warning window | Excel Discussion (Misc queries) | |||
Creating a Warning window | Excel Worksheet Functions | |||
Drop Down Window in Excel | Excel Discussion (Misc queries) |