excel - Data Entry form with vba codes

Question

I have an excel form in one sheet as data entry (1- Textbox1 = Name, 2-Textbox2 = Old, 3-Textbox3= Email )

question from:https://stackoverflow.com/questions/65870041/data-entry-form-with-vba-codes

Answer 1

You do not say anything to clarify my question... When come here and need help, it is good to clarify any question and if you do not understand what is it about to ask for clarifications.

Anyhow, I am trying helping you suggesting the next scenario:

1. I assumes that you name "an excel form in one sheet" the sheet itself, having three text boxes (ActiveX type) and a combo box (also ActiveX type).

The three text boxes will have the name "TextBox1", "TextBox2" and "TextBox3". If not so, the code which will follow would be easy to adapt at your real names.

The combo box name should be "ComboOptions". After creation, when in Design mode, right click on it, choose Properties and set A1:A2 at its ListFillRange property. Then add "Teachers" in "A1" and "Students" in "A2.

2. Paste the next code in a standard module:

Private Sub EnterButton_Click()
 Dim sh As Worksheet, sh2 As Worksheet, sh3 As Worksheet, workSh As Worksheet, lastRow As Long
 Dim cbOption As MSForms.ComboBox, txtName As MSForms.TextBox, txtOld As MSForms.TextBox, txtEmail As MSForms.TextBox

 Set sh = ActiveSheet
 Set sh2 = sh.Next 'Worksheets("Teachers")
 Set sh3 = sh2.Next 'Worksheets("Students")

 Set cbOption = sh.OLEObjects("ComboOptions").Object

 If cbOption.Value = "" Then MsgBox "No option chosen in combo box...": Exit Sub
 If cbOption.Value = "Teachers" Then
    Set workSh = sh2
 ElseIf cbOption.Value = "Students" Then
    Set workSh = sh3
 End If
 Set txtName = sh.OLEObjects("TextBox1").Object
 Set txtOld = sh.OLEObjects("TextBox2").Object
 Set txtEmail = sh.OLEObjects("TextBox3").Object
 If txtName.Text = "" Or txtOld.Text = "" Or txtEmail.Text = "" Then _
       MsgBox "All involved text boxes must have a value!": Exit Sub
 lastRow = workSh.Range("B" & workSh.rows.count).End(xlUp).row + 1
    With workSh
       .Range("B" & lastRow).Value = txtName.Value
       .Range("C" & lastRow).Value = txtOld.Value
       .Range("D" & lastRow).Value = txtEmail.Value
    End With
End Sub

3. Select one option in the combo box, run the above code and send some feedback about the behavior against your expectations.