VBA Combo Box - Select

[george hart]

Hi

The code blelow works great in that it opens then closes another workbok and allows for a dropdown box within a form. However, I need code that when I select a name from the dropdown box, the name I select is copied/transfered into a specific cell??

Select/double click the name and it appears in the next empty cell in column "B"

For example For x = 4 To Cells(Rows.Count, "B").End(xlUp).Row

Any ideas??

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ComboBox1
.Clear ' remove existing entries from the listbox
' turn screen updating off,
' prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Projects\Sickness\Traincrew Table.xls", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("B2:B3000").Value
' get the values you want
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)
' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
End Sub

Many thanks in advance

 

[Ruddles]

In design mode, select the combobox, then right-click and select View Code. From the object drop-down menu (top-left) select the name of the combobox and from the event drop-down menu (top-right) select Change.

Th event handler which has just been generated will trigger whenever something is selected from the combobox - you just need to supply the code which copies the value of your combobox to a worksheet cell. Something like this, perhaps:-

Range("A1")=ComboBox1.Value