【原】学生基本信息窗体录入VBA代码

Sub 保存数据()

Dim i As Integer

i = UserForm1.Label21.Caption + 1

    With Sheet2

        .Range("B" & i) = UserForm1.TextBox1.Text

        .Range("C" & i) = UserForm1.TextBox2.Text

        .Range("D" & i) = UserForm1.TextBox3.Text

        .Range("E" & i) = UserForm1.TextBox4.Text

        .Range("F" & i) = UserForm1.TextBox5.Text

        .Range("K" & i) = UserForm1.TextBox6.Text

        .Range("L" & i) = UserForm1.TextBox7.Text

        .Range("N" & i) = UserForm1.TextBox8.Text

        .Range("O" & i) = UserForm1.TextBox9.Text

        .Range("P" & i) = UserForm1.TextBox10.Text

        .Range("R" & i) = UserForm1.TextBox11.Text

        .Range("S" & i) = UserForm1.TextBox12.Text

        .Range("A" & i) = UserForm1.ComboBox7.Value

        .Range("G" & i) = UserForm1.ComboBox1.Value

        .Range("H" & i) = UserForm1.ComboBox2.Value

        .Range("I" & i) = UserForm1.ComboBox3.Value

        .Range("J" & i) = UserForm1.ComboBox4.Value

        .Range("M" & i) = UserForm1.ComboBox5.Value

        .Range("Q" & i) = UserForm1.ComboBox6.Value

    End With

End Sub

Sub 读取数据()

n = UserForm1.Label21.Caption

    With Sheet2

        UserForm1.TextBox1.Text = .Range("B" & n)

        UserForm1.TextBox2.Text = .Range("C" & n)

        UserForm1.TextBox3.Text = .Range("D" & n)

        UserForm1.TextBox4.Text = .Range("E" & n)

        UserForm1.TextBox5.Text = .Range("F" & n)

        UserForm1.TextBox6.Text = .Range("K" & n)

        UserForm1.TextBox7.Text = .Range("L" & n)

        UserForm1.TextBox8.Text = .Range("N" & n)

        UserForm1.TextBox9.Text = .Range("O" & n)

        UserForm1.TextBox10.Text = .Range("P" & n)

        UserForm1.TextBox11.Text = .Range("R" & n)

        UserForm1.TextBox12.Text = .Range("S" & n)

        UserForm1.ComboBox7.Value = .Range("A" & n)

        UserForm1.ComboBox1.Value = .Range("G" & n)

        UserForm1.ComboBox2.Value = .Range("H" & n)

        UserForm1.ComboBox3.Value = .Range("I" & n)

        UserForm1.ComboBox4.Value = .Range("J" & n)

        UserForm1.ComboBox5.Value = .Range("M" & n)

        UserForm1.ComboBox6.Value = .Range("Q" & n)

    End With

End Sub

Sub 数据重置()

        UserForm1.TextBox1.Text = ""

        UserForm1.TextBox2.Text = ""

        UserForm1.TextBox3.Text = ""

        UserForm1.TextBox4.Text = ""

        UserForm1.TextBox5.Text = ""

        UserForm1.TextBox6.Text = ""

        UserForm1.TextBox7.Text = ""

        UserForm1.TextBox8.Text = ""

        UserForm1.TextBox9.Text = ""

        UserForm1.TextBox10.Text = ""

        UserForm1.TextBox11.Text = ""

        UserForm1.TextBox12.Text = ""

'        UserForm1.ComboBox7.Value = "请选择"

'        UserForm1.ComboBox1.Value = "请选择"

'        UserForm1.ComboBox2.Value = "请选择"

'        UserForm1.ComboBox3.Value = "请选择"

'        UserForm1.ComboBox4.Value = "请选择"

'        UserForm1.ComboBox5.Value = "请选择"

'        UserForm1.ComboBox6.Value = "请选择"

End Sub

------------------------------------------------

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

---------------------------------------

Private Sub Label19_Click()

Call 保存数据

Call 数据重置

Me.Label21.Caption = Sheet2.Range("A65536").End(xlUp).Row

End Sub

Private Sub Label22_Click()

Call 读取数据

 UserForm1.Label21.Caption = UserForm1.Label21.Caption - 1

    If UserForm1.Label21.Caption <= 1 Then

    Label22.Enabled = False

    Else

    Label22.Enabled = True

    End If

End Sub

Private Sub Label23_Click()

Call 读取数据

 UserForm1.Label21.Caption = UserForm1.Label21.Caption + 1

End Sub

Private Sub TextBox12_Change()

Sheet1.Range("E1") = Me.TextBox12.Value

Me.Label26.Caption = Sheet1.Range("F1")

End Sub

Private Sub TextBox2_Change()

Sheet1.Range("A1") = Me.TextBox2.Value

Me.Label24.Caption = Sheet1.Range("B1")

End Sub

Private Sub TextBox9_Change()

Sheet1.Range("C1") = Me.TextBox9.Value

Me.Label25.Caption = Sheet1.Range("D1")

End Sub

Private Sub UserForm_Activate()

Me.Label21.Caption = Sheet2.Range("A65536").End(xlUp).Row

End Sub

Private Sub UserForm_Initialize()

 ComboBox1.List() = Array("非农业户口", "农业户口")

 ComboBox2.List() = Array("是", "否")

 ComboBox3.List() = Array("是", "否")

 ComboBox4.List() = Array("是", "否")

ComboBox5.List() = Array("父亲", "母亲", "祖父母或外祖父母", "兄妹", "其他")

ComboBox6.List() = Array("父亲", "母亲", "祖父母或外祖父母", "兄妹", "其他")

ComboBox7.List() = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10")

End Sub