How do you transfer data from combo box on userform to worksheet?!

[dnomyar]

I have my userform set up with all the combo boxes. One of the Combo boxes(NamesDropDown) includes the names of all the students. As stated above each of the 6 combo boxes has the list of courses. I also have other text boxes in the same userform that will collect grades. What I need is that everytime I click on my submit button, all the information will be transferred to a spreadsheet(Dates). This spreadsheet collects the dates that the students passed the course.

I need it to know that based on the student's name(picked from NameDropDown and ALREADY listed under column "C" on the worksheet) it will provide the date that the course was completed appropriately. The courses are listed from left to right on columns "D" through "P".

The students names are already listed on the spreadsheet by referencing a different range. I want the submit button to be smart enough to allocate the data on its own. I've done different versions of this code before, but for some reason I just don't know where to start now. Please Help!... 

On the UserForm the date the course was passed is taken from DateBox.

 

[dreid1011]

Worksheets("Sheet1").Range("A1").Value = ComboBox.Value

You can try something like that. I know list boxes work with .Value, I imagine comboboxes are similar.

 

[dnomyar]

Worksheets("Sheet1").Range("A1").Value = ComboBox.Value

You can try something like that. I know list boxes work with .Value, I imagine comboboxes are similar.

Thank you for the ridiculously fast response!!... That was awesome. I think less than 2 minutes, however that is very similar to what I started with, so I know it will not do. Again, the names are in column "C" and the courses are listed from "D" to "P". Since the names are static/stationary in column "C", I need the code to automatically assign course completion dates corresponding to the specific course AND the students name. Thanks again, though!

 

[diddi]

assuming that you have populated the combobox for student names from the column C, the first thing is to check the .listindex for the student selected. that will give you the row into which your dates will be stored. then as you check each other selection you can see what columns you need to transfer the date to using

Sheets("mysheet").cells(NamesDropDown.listindex+1, mycolumn)=textbox.value

code needed to give more specific info

 

[dnomyar]

You guys are just so great . Diddi, I actually understand what you posted. I have since left the workbook, but I will apply your advice as soon as I get back. Thank you so much. If I can't get it to work, I will post the code that I have so far.

 

[diddi]

VG. its all a learning curve. explanation better than spoon feeding I believe

 

[dnomyar]

assuming that you have populated the combobox for student names from the column C, the first thing is to check the .listindex for the student selected. that will give you the row into which your dates will be stored. then as you check each other selection you can see what columns you need to transfer the date to using

Sheets("mysheet").cells(NamesDropDown.listindex+1, mycolumn)=textbox.value

code needed to give more specific info

I am posting all the code that I have for my User Form. Again, what I am trying to do is:
Having the names of the students listed under Column "C" and all the courses listed on Row 1 starting on Column "D", the code will detect the student's name, find the row under Column "C" that contains that student's name and then apply the date entered in the user form somewhere after the student's name(on the same row, just a different column... the column where the course title is the first cell of the column). It's practically like playing Battleship. Again, the course titles are listed as math, english, spanish, science... starting with Column "D", "E", and so on The date comes from my datebox on the userform. Thank you to the person that can figure this out. I'm not the best at this. Please look at the red text... that's where the problem lies.

Course1, Course2, Course3, Course4, Course5, and Course6 are the combo boxes.

Private Sub Course1_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course1.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course1.ListIndex > -1 Then ScoreBox1.SetFocus

End Sub
---
Private Sub Course1_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course1.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Function CreateArray(r As Range)

Dim col As New Collection, c As Range, TempArray(), i As Long

For Each c In r

On Error Resume Next

col.Add c.Value, CStr(c.Value)

If Err.Number = 0 And Trim(c) <> "" Then

ReDim Preserve TempArray(i)

TempArray(i) = c.Value

i = i + 1

End If

Err.Clear

Next

CreateArray = TempArray

Erase TempArray

End Function
---
Private Sub Course2_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course2.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course2.ListIndex > -1 Then ScoreBox2.SetFocus

End Sub
---
Private Sub Course2_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course2.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Private Sub Course3_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course3.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course3.ListIndex > -1 Then ScoreBox3.SetFocus

End Sub
---
Private Sub Course3_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course3.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Private Sub Course4_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course4.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course4.ListIndex > -1 Then ScoreBox4.SetFocus

End Sub
---
Private Sub Course4_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course4.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Private Sub Course5_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course5.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course5.ListIndex > -1 Then ScoreBox5.SetFocus

End Sub
---
Private Sub Course5_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course5.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Private Sub Course6_Change()

Dim Found As Range

Set Found = Worksheets("Data Entry").Columns("AZ").Find(what:=Me.Course6.Value, LookIn:=xlValues, lookat:=xlWhole)

Found.Delete

If Course6.ListIndex > -1 Then ScoreBox6.SetFocus

End Sub
---
Private Sub Course6_DropButt*******()

Dim LR As Long

LR = Cells(Rows.Count, "AZ").End(xlUp).Row

Course6.List() = CreateArray(Range("AZ2:AZ56" & LR))

End Sub
---
Private Sub DateButton_Click()

DateBox.Text = Date

End Sub
---
Private Sub SubmitButton_Click()

Dim LastRow As Long

Dim ws As Worksheet

Dim r As Range

Set ws = Sheets("Data Entry")<o></o>

For Each r In
Range("C2:C56")

If r.Value = StudentNameDropDown.Text Then

LastRow = (r & Range("D"))

ws.Range("D" & LastRow).Value = DateBox.Value

End With?

Worksheets("Data Entry").Range("CoursesDuplicate").Value = Worksheets("Ranges-Lists").Range("Courses").Value

End Sub
---
Private Sub UserForm_Initialize()

Dim cell As Range

With Worksheets("Ranges-Lists")

For Each cell In .Range("I2:I50" & .Cells(Rows.Count,
3).End(xlUp).Row)

If Not IsEmpty(cell) Then InstructorDropDown.AddItem cell.Value

Next cell

End With<o></o>

With
Worksheets("Ranges-Lists")

For Each cell In .Range("D2:D50" & .Cells(Rows.Count,
3).End(xlUp).Row)

If Not IsEmpty(cell) Then StudentNameDropDown.AddItem cell.Value

Next cell

End With
End Sub
---
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Worksheets("Data Entry").Range("CoursesDuplicate").Value = Worksheets("Ranges-Lists").Range("Courses").Value

End Sub<o></o>

 

[dnomyar]

diddi, that piece of code does fairly well except i need to do a "search" or "comparison" on it's own and determing which column to use based on the choices made at all 6 comboboxes. Will this be an array?