Combobox.value reference

[f2e4]

Hi people,

Summary:

4 worksheets
sheet 1 = 2 comboboxes
sheet 2 = list of people
sheet 3 = list of projects
sheet 4 = summary of people and their projects

Combobox1 = list of the staff surnames (Column C - Sheet 2)
Combobox2 = list of projects (Column C - Sheet 3)

I am trying to write some code that will take the value selected from combobox1 and select the row of data (Column B:F) where that value came from in sheet 2 and paste it into A1 of sheet 4

and the same for combobox2 but for projects and to paste it after the staff details in sheet 4 (Example sheet4: staff details A : D, project details E : H)

Thanks again for your help

[mikerickson]

What kind of combo boxes, Forms Menu or ActiveX from the Toolbox?

If they are from the Forms menu, this should work.
[VBA]Dim PeopleDropDown As Object
Dim projectDropDown As Object
Dim destinationPlace As Range

Set PeopleDropDown = ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 1"): Rem adjust
Set projectDropDown = ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 2"): Rem adjust
Set destinationPlace = ThisWorkbook.Sheets("Sheet4").Range("A65536").End(xlUp).Offset(1, 0): Rem adjust

With PeopleDropDown.ControlFormat
destinationPlace.Resize(1, 4).Value = Range(.ListFillRange).Cells(.ListIndex, 1).Offset(0, -2).Resize(1, 4).Value
End With

With projectDropDown.ControlFormat
destinationPlace.Offset(0, 4).Resize(1, 4).Value = Range(.ListFillRange).Cells(.ListIndex, 1).Offset(0, -2).Resize(1, 4).Value
End With[/VBA]

[f2e4]

They are activex controls but i ended up using the following code:

I'm aware that we should avoid Loop functions at all cost because they are so slow, but i just couldn't think of any other way around it.

[vba]
Sub Add_Member_to_Project()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

staffdropdown = ActiveSheet.ComboBox6.Value
projectdropdown = ActiveSheet.ComboBox7.Value

'Find the staff value stored in the combobox
Sheets("Staff").Select
x = 4
Do
x = x + 1

Loop Until Cells(x, 3) = staffdropdown

staffnumber = Cells(x, 2)
staffname = Cells(x, 4)
stafflocation = Cells(x, 6)

'Find the project value stored in the combobox
Sheets("Projects").Select
y = 4
Do
y = y + 1
Loop Until Cells(y, 3) = projectdropdown

projnum = Cells(y, 2)
projman = Cells(y, 3)
projstat = Cells(y, 4)

Sheets("Workload").Select

'Find last active row in sheet to store values
Dim NextRow As Long
With ActiveSheet
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

Cells(NextRow, 2) = projnum
Cells(NextRow, 3) = projectdropdown
Cells(NextRow, 4) = projman
Cells(NextRow, 5) = projstat
Cells(NextRow, 6) = staffnumber
Cells(NextRow, 7) = staffname
Cells(NextRow, 8) = staffdropdown
Cells(NextRow, 9) = stafflocation

Application.ScreenUpdating = True
End Sub
[/vba]

[Djeetn]

To fasten your loop you could use the VLookup function of excel.

So you're code would look like this:

[vba]
Sub Add_Member_to_Project()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

staffdropdown = ActiveSheet.ComboBox6.Value
projectdropdown = ActiveSheet.ComboBox7.Value

'Find the staff value stored in the combobox
Dim wsStaff As Worksheet
Set wsStaff = Application.ActiveWorkbook.Worksheets("Staff")

Dim rngStaff As Range
Set rngStaff = wsStaff.Range("....") ' Fill in the Vertical Lookup range, i.e. give the table a range name on the sheet

staffnumber = Application.WorksheetFunction.VLookup(staffdropdown, rngStaff, 2, False)
staffname = Application.WorksheetFunction.VLookup(staffdropdown, rngStaff, 3, False)
stafflocation = Application.WorksheetFunction.VLookup(staffdropdown, rngStaff, 4, False)

'Find the project value stored in the combobox
Dim wsProject As Worksheet
Set wsProject = Application.ActiveWorkbook.Worksheets("Project")

Dim rngProject As Range
Set rngProject = wsStaff.Range("....") ' Fill in the Vertical Lookup range, i.e. give the table a range name on the sheet

projnum = Application.WorksheetFunction.VLookup(projectdropdown, rngProject , 2, False)
projman = Application.WorksheetFunction.VLookup(projectdropdown, rngProject , 3, False)
projstat = Application.WorksheetFunction.VLookup(projectdropdown, rngProject , 4, False)

Sheets("Workload").Select

'Find last active row in sheet to store values
Dim NextRow As Long
With ActiveSheet
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

Cells(NextRow, 2) = projnum
Cells(NextRow, 3) = projectdropdown
Cells(NextRow, 4) = projman
Cells(NextRow, 5) = projstat
Cells(NextRow, 6) = staffnumber
Cells(NextRow, 7) = staffname
Cells(NextRow, 8) = staffdropdown
Cells(NextRow, 9) = stafflocation

Application.ScreenUpdating = True
End Sub
[/vba]

Make sure that staffdropdown and projectdropdown are in the 1st column of your lookup tables (swith it with the staffnum and projectnum).

Regards,

Dieter

[mikerickson]

If a ListBox is filled from a (column) range, there is no need to search for the cell containing selected List Item. FillRange.Range("A1").Offset(.ListIndex,0) is the cell that the selected item came from.

[f2e4]

both projects and staff names are from a dynamic name range (using offset function)

How do i modify the above code to use your suggestions

[mikerickson]

Use the Application.Index function to isolate the column to search
[VBA]With Range("myDynamicRange")
MsgBox .Cells(Application.Match(searchValue, Application.Index(.Value,0,ColNum))).Address " is where searchValue is found.
End With[/VBA]

[f2e4]

you've lost me a bit

how did i add your code above to my code in my very 1st post and what code do i remove from my original version

[mikerickson]

The code I posted will return the row number (relative to the dynamic range) of the item sought.

Edit, somehow the ListBox got lost in all of this. My observation that if a list box is filled from a Range, the .ListIndex will return the row number of the selected item.