VBA Combo Box - Select

george hart

Board Regular
Joined
Dec 4, 2008
Messages
241
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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:-
Code:
Range("A1")=ComboBox1.Value
 

Forum statistics

Threads
1,141,847
Messages
5,708,959
Members
421,601
Latest member
Garlo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top