Userform autopoulting cells from a single list box

Question

I am creating a user form that populates a row of data into a workssheet everytime it is filled out and a button is clicked.

at the moment i have two list boxes in the userform that takes the data from the same worksheet as it populates. they are:

NAME COUNTRY Anne UK George USA Jack France James Spain

These lists relate to each other (i.e. Anne manages UK, George manages USA etc) I want to be able to remove one of the listbox from the userform and have it auto poulate the worksheet.

at the moment i have the code below.

Private Sub Enter_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Planning")

'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _

 .End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 1).Value = Date ws.Cells(iRow, 2).Value = Me.Name.Value ws.Cells(iRow, 3).Value = Me.Country.Value ws.Cells(iRow, 4).Value = Me.TextBox1.Value ws.Cells(iRow, 5).Value = Me.ListBox3.Value ws.Cells(iRow, 6).Value = Me.TextBox2.Value

I am thinking i will need to change the "ws.Cells(iRow, 3).Value = ????" but not sure to what.

any ideas ????

Answer

George

It should be pretty easy. How do the lists relate to each other? Where doest the data in the list boxes come from? The easiest thing to to do would probably be to have a list somewhere on the worksheet that has a column of name and an adjacent column of countries. You could use the value of the first list box to lookup the appropriate country; something like

ws.cells(irow,3).value=application.vlookup(me.name.value,range with names/countries, 2, false)

If the relationship between name and country can only come from the listboxes, you could do something like, if the names and countries are in the same position in the list boxes

Private Sub Namebox_Click()

Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Planning")

'find first empty row in database

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 2).Value = Me.Namebox.Value ws.Cells(iRow, 3).Value = Country.List(Namebox.ListIndex)

End Sub