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.
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

[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.


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

[Djeetn]

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

So you're code would look like this:


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


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
With Range("myDynamicRange")
MsgBox .Cells(Application.Match(searchValue, Application.Index(.Value,0,ColNum))).Address " is where searchValue is found.
End With

[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.