Excel vba

20101版本啟用vba
檔案-->選項-->自訂功能區-->V開發人員

Private Sub ComboBox2_Change()

If (ComboBox2.Value = "新案" Or ComboBox2.Value = "檢卷") Then TextBox4.Text = "事務官" Else TextBox4.Text = "書記官"
If ComboBox2.Value = "新案" Then TextBox6.Text = TextBox3.Text Else TextBox6.Text = ""

End Sub

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

Private Sub CommandButton1_Click()

Range("B1").Select
Selection.End(xlDown).Select

If (TextBox6.Text) <> "" Then Sheets("案號管理").Range("A" & ActiveCell.Row + 1).Value = TextBox6.Value

Sheets("案號管理").Range("B" & ActiveCell.Row + 1).Value = TextBox1.Value
Sheets("案號管理").Range("C" & ActiveCell.Row + 1).Value = ComboBox1.Value
Sheets("案號管理").Range("D" & ActiveCell.Row + 1).Value = TextBox2.Value
Sheets("案號管理").Range("E" & ActiveCell.Row + 1).Value = ComboBox2.Value
Sheets("案號管理").Range("F" & ActiveCell.Row + 1).Value = TextBox3.Value

Sheets("案號管理").Range("G" & ActiveCell.Row + 1).Value = TextBox4.Value

Sheets("案號管理").Range("H" & ActiveCell.Row + 1).Value = TextBox5.Value


Selection.End(xlDown).Select

CommandButton1.Enabled = False
CommandButton2.Enabled = False

End Sub


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

Private Sub CommandButton2_Click()
       ActiveCell.Offset(0, 3).Value = ComboBox2.Value
       ActiveCell.Offset(0, 4).Value = TextBox3.Value
       ActiveCell.Offset(0, 5).Value = TextBox4.Value
       ActiveCell.Offset(0, 6).Value = TextBox5.Value
       ActiveCell.Offset(0, -1).Value = TextBox6.Value
       CommandButton1.Enabled = False
       CommandButton2.Enabled = False

End Sub


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

Private Sub CommandButton3_Click()

ComboBox1.Value = ""
TextBox2.Value = ""
ComboBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""

TextBox1.SetFocus

End Sub

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




Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Range("B2").Select
Dim i As Integer
i = 0

Do While Not (ActiveCell.Offset(i, 0).Value = "")
   
    If (ActiveCell.Offset(i, 0).Value = Val(TextBox1.Value) And ActiveCell.Offset(i, 1).Value = ComboBox1.Value And ActiveCell.Offset(i, 2).Value = Val(TextBox2.Value)) Then
     
       ComboBox2.Value = ActiveCell.Offset(i, 3).Value
       TextBox3.Value = ActiveCell.Offset(i, 4).Value
       TextBox4.Value = ActiveCell.Offset(i, 5).Value
       TextBox5.Value = ActiveCell.Offset(i, 6).Value
       TextBox6.Value = ActiveCell.Offset(i, -1).Value
     
       CommandButton1.Enabled = False
       CommandButton2.Enabled = True
     
       ActiveCell.Offset(i, 0).Select
     

        Exit Do
    Else
        i = i + 1
    End If
   
    CommandButton1.Enabled = True
    CommandButton2.Enabled = False

Loop
End Sub




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


Private Sub TextBox3_Change()

If ComboBox2.Value = "新案" Then TextBox6.Text = TextBox3.Text Else TextBox6.Text = ""

End Sub



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



Private Sub UserForm_Activate()
TextBox1.Text = Year(Date) - 1911
CommandButton1.Enabled = False
CommandButton2.Enabled = False

Dim i As Integer
i = 1
Do While Not (Sheets("dataSource").Range("A" & i) = "")
     ComboBox1.AddItem (Sheets("dataSource").Range("A" & i))
    i = i + 1
Loop


Dim j As Integer
j = 1
Do While Not (Sheets("dataSource").Range("C" & j) = "")
     ComboBox2.AddItem (Sheets("dataSource").Range("C" & j))
    j = j + 1
Loop

End Sub


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