Hsieh

1. Private Sub Label1_Click()
   
2. Set a = Columns("A:A").Find(TextBox1.Text, lookat:=xlWhole)
   
3. If a Is Nothing Then r = [A65536].End(xlUp).Row + 1 Else r = a.Row
   
4. For i = 1 To 9
   
5.    Cells(r, i) = Controls("TextBox" & i).Text
   
6. Next
   
7. End Sub
   
8. 
   
9. Private Sub Label2_Click()
   
10. Set a = Columns("A:A").Find(TextBox1.Text, lookat:=xlWhole)
    
11. If a Is Nothing Then r = [A65536].End(xlUp).Row + 1 Else r = a.Row
    
12. For i = 1 To 9
    
13.    Cells(r, i) = Controls("TextBox" & i).Text
    
14. Next
    
15. 
    
16. End Sub
    
17. 
    
18. Private Sub Label3_Click()
    
19. ThisWorkbook.Save
    
20. End Sub
    
21. 
    
22. Private Sub Label4_Click()
    
23. ScrollBar1.Value = Application.Max(ScrollBar1.Value - 1, ScrollBar1.Min)
    
24. End Sub
    
25. 
    
26. Private Sub Label5_Click()
    
27. ScrollBar1.Value = Application.Min(ScrollBar1.Value + 1, ScrollBar1.Max)
    
28. If ScrollBar1.Value = ScrollBar1.Max Then ScrollBar1.Max = ScrollBar1.Max + 1
    
29. End Sub
    
30. 
    
31. Private Sub ScrollBar1_Change()
    
32. r = ScrollBar1.Value
    
33. For i = 1 To 9
    
34. Controls("TextBox" & i) = Cells(r, i).Value
    
35. Next
    
36. End Sub
    
37. 
    
38. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
39. Set a = Columns("A:A").Find(TextBox1.Text, lookat:=xlWhole)
    
40. If Not a Is Nothing Then
    
41. MsgBox "學號重複"
    
42. With TextBox1
    
43. Cancel = True
    
44. End With
    
45. End If
    
46. End Sub
    
47. 
    
48. Private Sub UserForm_Initialize()
    
49. r = [A65536].End(xlUp).Row
    
50. With ScrollBar1
    
51. .Min = 3
    
52. .Max = r + 1
    
53. End With
    
54. End Sub

複製代碼

回復 1# 周大偉

Hsieh

回復 3# 周大偉
增加一個刪除程序

1. Private Sub Label15_Click()
   
2. Set a = Columns("A:A").Find(TextBox1.Text, lookat:=xlWhole)
   
3. If Not a Is Nothing Then a.Resize(, 9).Delete xlShiftUp
   
4. End Sub

複製代碼

要刪除或修改都請使用上一筆、下一筆或捲軸讓學號為空白後再輸入