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:
- 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.
- 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
- Select one option in the combo box, run the above code and send some feedback about the behavior against your expectations.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…