Combobox to tell what worksheet the data should go.

[JeremySun]

Hello,

I would like to know if it is possible to have a combobox answer determin what worksheet the userform information goes to. Example:

Combobox options are : English, Math, Science, and AP
My worksheets are: English, Math, Science, and AP

The user form contains student's first name, last name, address, e-mail, phone, etc....

I would like to have one workbook for all the information. Below is what I have

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ""
If txtfirstname.Value = "" Or txtrank.Value = "" _
Or txtemail.Value = "" Or txtlastname.Value = "" Or txtworkplace.Value = "" Or txtssn.Value = "" _
Or txtphone.Value = "" Then
MsgBox Prompt:="All fields are required", _
Title:="REMINDER"
End If
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtfirstname.Value
ws.Cells(iRow, 2).Value = Me.txtlastname.Value
ws.Cells(iRow, 3).Value = Me.txtrank.Value
ws.Cells(iRow, 4).Value = Me.txtemail.Value
ws.Cells(iRow, 5).Value = Me.txtworkplace.Value
ws.Cells(iRow, 6).Value = Me.txtssn.Value
ws.Cells(iRow, 7).Value = Me.txtphone.Value
ws.Cells(iRow, 8).Value = Me.txtdate.Value
ws.Cells(iRow, 9).Value = Me.cbtarget.Value    <----class names....ENGLISH,MATH,,,,,


'clear the data
Me.txtfirstname.Value = ""
Me.txtlastname.Value = ""
Me.txtrank.Value = ""
Me.txtemail.Value = ""
Me.txtworkplace.Value = ""
Me.txtssn.Value = ""
Me.txtphone.Value = ""

End Sub

 

[Ralajer]

Jeremy welcome to the boards.

Replace Set ws = "" with this.

    set ws = Nothing
    If cbtarget.Text <> "" Then
        Set ws = Sheets(cbtarget.Text)
    End If

Also add Or ws Is Nothing to the validation if statement.

 

[JeremySun]

Thank you for your quick response, it works like a charm.How may I close this thread?

 

[Ralajer]

Good to hear it works. I'm fairly certain that a board member without admin/mod privileges can't close a thread. Common practice is to leave a thread open and to responded that your question was answered.