803 note

Private Sub CommandButton1_Click()
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim total As Integer
    Dim avg As Single
    Dim ac As Integer
 
    Dim rcount As Integer
'    rcount = Worksheets("工作表1").UsedRange.Rows.Count
'    MsgBox rcount
    Range("A1").Select
    ac = ActiveCell.Row
'   MsgBox ac

    If IsEmpty(Range("A" & (ac + 1))) = True Then
        rcount = 1
        MsgBox "a"
    Else
         rcount = Worksheets("工作表1").UsedRange.Rows.Count
            MsgBox "b"

    End If
    MsgBox rcount
 
   If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then
        a = CInt(TextBox1.Value)
        b = CInt(TextBox2.Value)
        c = CInt(TextBox3.Value)
        total = a + b + c
        TextBox4.Value = CStr(total)
     
        avg = total / 3
        TextBox5.Value = CStr(Format(avg, ".00"))
     
        Sheets(1).Cells(rcount + ac, 1).Value = TextBox1.Value
        Sheets(1).Cells(rcount + ac, 2).Value = TextBox2.Value
        Sheets(1).Cells(rcount + ac, 3).Value = TextBox3.Value
        Sheets(1).Cells(rcount + ac, 4).Value = TextBox4.Value
        Sheets(1).Cells(rcount + ac, 5).Value = TextBox5.Value
    Else
        MsgBox "資料不完整請重新輸入"
        If TextBox1.Value = "" Then
            TextBox1.Activate
        ElseIf TextBox2.Value = "" Then
            TextBox2.Activate
        ElseIf TextBox3.Value = "" Then
            TextBox3.Activate
        End If
     
    End If
 
 
End Sub




Private Sub CommandButton2_Click()
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
 
End Sub

Private Sub CommandButton3_Click()
    Dim score As String
 
    score = TextBox6.Value
    Sheets(1).Range("A1").AutoFilter field:=4, Criteria1:=score
    查詢範圍.AutoFilter   Field:=欄位位置,criteria1:=條件值,criteria2:=條件值
End Sub

Private Sub CommandButton4_Click()
    Sheets(1).Range("A1").AutoFilter
End Sub

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

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        TextBox2.Activate

    End If

    

End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        TextBox3.Activate

    End If

    

End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        TextBox1.Activate

    End If

    

End Sub

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

Private Sub CommandButton1_Click()

    Call saveworkdata

    

End Sub

Public Sub saveworkdata()

    Dim a As Integer

    Dim b As Integer

    Dim c As Integer

    Dim total As Integer

    Dim avg As Single

    Dim ac As Integer

    

    Dim rcount As Integer

'    rcount = Worksheets("工作表1").UsedRange.Rows.Count

'    MsgBox rcount

    Range("A1").Select

    ac = ActiveCell.Row

'   MsgBox ac

    If IsEmpty(Range("A" & (ac + 1))) = True Then

        rcount = 1

    Else

         rcount = Worksheets("工作表1").UsedRange.Rows.Count

    End If

'    MsgBox rcount

    

   If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then

        a = CInt(TextBox1.Value)

        b = CInt(TextBox2.Value)

        c = CInt(TextBox3.Value)

        total = a + b + c

        TextBox4.Value = CStr(total)

        

        avg = total / 3

        TextBox5.Value = CStr(Format(avg, ".00"))

        

        Sheets(1).Cells(rcount + ac, 1).Value = TextBox1.Value

        Sheets(1).Cells(rcount + ac, 2).Value = TextBox2.Value

        Sheets(1).Cells(rcount + ac, 3).Value = TextBox3.Value

        Sheets(1).Cells(rcount + ac, 4).Value = TextBox4.Value

        Sheets(1).Cells(rcount + ac, 5).Value = TextBox5.Value

    Else

        MsgBox "資料不完整請重新輸入"

        If TextBox1.Value = "" Then

            TextBox1.Activate

        ElseIf TextBox2.Value = "" Then

            TextBox2.Activate

        ElseIf TextBox3.Value = "" Then

            TextBox3.Activate

        End If

        

    End If

    

End Sub

Private Sub CommandButton2_Click()

    Call thingdata

    

End Sub

Private Sub CommandButton3_Click()

    Dim score As String

    

    score = TextBox6.Value

    Sheets(1).Range("A1").AutoFilter field:=4, Criteria1:=score

End Sub

Private Sub CommandButton4_Click()

    Sheets(1).Range("A1").AutoFilter

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        TextBox2.Activate

    End If

    

End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        TextBox3.Activate

    End If

    

End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then

        Call saveworkdata

        Call thingdata

        TextBox1.Activate

    End If

    

End Sub

Public Sub thingdata()

    TextBox1.Value = ""

    TextBox2.Value = ""

    TextBox3.Value = ""

    TextBox4.Value = ""

    TextBox5.Value = ""

    

End Sub