GBKEE

¦^´_ 6# lifedidi

1.     Dim d1 As Object, d2 As Object
   
2.     Dim Rng As Range
   
3. Private Sub ListBox1_Change()
   
4.     If ListBox1.Text = "" Then Exit Sub
   
5.     arr = Filter(d2.keys, ListBox1.Value)
   
6.     ListBox2.Clear
   
7.     For i = 0 To UBound(arr)
   
8.         ListBox2.AddItem Replace(arr(i), ListBox1.Value & "-", "")
   
9.     Next i
   
10.     For i = 2 To 4
    
11.         UserForm1.Controls("TextBox" & i).Value = ""
    
12.     Next i
    
13. End Sub
    
14. Private Sub ListBox2_Change()
    
15.     If ListBox2.Text = "" Then Exit Sub
    
16.     R = d2(ListBox1.Value & "-" & ListBox2.Value)
    
17.     'ec = (Rng, 1)
    
18.     For c = 3 To Rng.Rows.Count
    
19.         UserForm1.Controls("TextBox" & c - 1).Value = Rng(R, c).Text
    
20.     Next c
    
21. End Sub
    
22. Private Sub UserForm_Initialize()
    
23.     Set d1 = CreateObject("Scripting.Dictionary")
    
24.     Set d2 = CreateObject("Scripting.Dictionary")
    
25.     With Sheets("sheet1")
    
26.       Set Rng = .[A1].CurrentRegion
    
27.     End With
    
28.     For R = 2 To Rng.Rows.Count
    
29.         mycase = "-" & Rng(R, 2)
    
30.         If Trim(Rng(R, 1)) <> "" Then
    
31.             myname = Trim(Rng(R, 1))
    
32.             br = R
    
33.             d1(myname) = R & "-" & R
    
34.         Else
    
35.             d1(myname) = br & "-" & R
    
36.         End If
    
37.         d2(myname & mycase) = R
    
38.     Next R
    
39.     UserForm1.ListBox1.List = d1.keys
    
40. End Sub

½Æ»s¥N½X

lifedidi

¦^´_ 4# Hsieh


    ¤j¤j§A¦n¡G
½Ð°Ñ¦Òªþ¥óÀÉ¡A
¦p¦Pªþ¥óTEXTBOX4¹ïÀ³Àx¦s®æEÄ檺¸ê®Æ¡A
¦ý¬O¦bUSERFORMÅã¥Ü¥X¨Ó«o¬O¼Æ­È¡A
¦p¦ó¯àÅý¥L¬°®É¶¡©O¡H

QQQ.rar (21.07 KB)

¤U¸ü¦¸¼Æ: 5

2013-4-26 14:00

GBKEE

¦^´_ 3# lifedidi

1. TextBox1 = ActiveSheet.Range("D1").Text

½Æ»s¥N½X

Hsieh

¦^´_ 3# lifedidi

1. Private Sub CommandButton1_Click()  '­û¤uÃB«×¬d¸ß¶s
   
2.     Dim a As Range, hh#
   
3.     With Sheets("SHEET3")
   
4.     hh = Application.SumIf(.[C:C], ComboBox1, .[D:D])
   
5.     End With
   
6.     TextBox2.Value = Application.Text(hh, "[hh]:mm")
   
7.     Set a = Sheet2.[B:B].Find(ComboBox1).Offset(, 1)
   
8.     TextBox1 = a.Text
   
9. TextBox3.Value = Application.Text(a - hh, "[hh]:mm") '³Ñ¾lÃB«×
   
10. End Sub

½Æ»s¥N½X

lifedidi

Dear GBKEE ¤j¤j¡G

·PÁ§AªºÀ°¦£¡I¡I

½Ð°Ý¤@¤UQ2ªº°ÝÃD

¦buserform¸Ìªºtextbox­nÅã¥Ü[hh]:mm¸Ó«ç»òªí¥Ü©O¡H
¡i§Ú¦³¹ïÀ³¨ìÀx¦s®æªº¸ê®Æ¡j

Á|¨Ò¡G
Àx¦s®æD1¸ê®Æ¬°¡i500:00¡j[hh]:mm
¦buserform¸Ìªºtextbox­nÅã¥Ü¡i500:00¡j
³o¼Ëªº®æ¦¡¸Ó¦p¦ó½s¼g©O¡H
3q~

GBKEE

¦^´_ 1# lifedidi

1. Private Sub UserForm_Initialize()  '­û¤uÃB«×ªí³æ
   
2.     ¦WºÙ¨î©w
   
3. End Sub
   
4. Private Sub ComboBox1_Change()
   
5.     With Sheets("Sheet1")
   
6.         .AutoFilterMode = False
   
7.         .Range("A1").AutoFilter FIELD:=3, Criteria1:=ComboBox1
   
8.         TextBox1 = ""
   
9.         TextBox2 = ""
   
10.         TextBox3 = ""
    
11.     End With
    
12. End Sub
    
13. Private Sub CommandButton1_Click()  '¬d¸ß¶s
    
14.     If ComboBox1.ListIndex = -1 Then Exit Sub
    
15.     Dim t(1 To 2) As Date, Rng As Range
    
16.     Set Rng = Sheet2.[B:B].Find(ComboBox1, LookAT:=xlWhole)
    
17.     If Rng Is Nothing Then
    
18.         MsgBox "¦WºÙ¿ù»~", vbCritical + vbOKOnly, ""
    
19.        Exit Sub
    
20.     End If
    
21.     t(1) = Rng.Offset(, 1)
    
22.     TextBox1 = Application.Text(t(1), "[hh]:mm")
    
23.     t(2) = Application.Sum(Sheet1.Range("D:D").SpecialCells(xlCellTypeVisible))
    
24.     TextBox2 = Application.Text(t(2), "[hh]:mm")
    
25.     With TextBox3
    
26.         .ForeColor = vbBlack
    
27.         .Text = Application.Text(IIf(t(1) > t(2), t(1) - t(2), t(2) - t(1)), "[hh]:mm")
    
28.         If t(2) > t(1) Then
    
29.             .Text = "-" & .Text
    
30.             .ForeColor = vbRed
    
31.         End If
    
32.      End With
    
33. End Sub
    
34. Private Sub ¦WºÙ¨î©w()
    
35.     With Sheets("SHEET1")
    
36.         .Cells(1, .Columns.Count).EntireColumn.Clear
    
37.         .[A1].CurrentRegion.Columns(3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
    
38.         ComboBox1.List = .Range(.Cells(2, .Columns.Count), .Cells(2, .Columns.Count).End(xlDown)).Value
    
39.     End With
    
40. End Sub
    
41. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
42.     Sheets("Sheet1").AutoFilterMode = False
    
43. End Sub

½Æ»s¥N½X

1. Private Sub UserForm_Initialize() '¤u¨ãÃB«×ªí³æ
   
2.     ¦WºÙ¨î©w
   
3. End Sub
   
4. Private Sub ComboBox1_Change()
   
5.    With Sheets("Sheet1")
   
6.         .AutoFilterMode = False
   
7.         .Range("A1").AutoFilter FIELD:=2, Criteria1:=ComboBox1
   
8.         TextBox1 = ""
   
9.         TextBox2 = ""
   
10.         TextBox3 = ""
    
11.     End With
    
12. End Sub
    
13. Private Sub CommandButton1_Click()  '¬d¸ß¶s
    
14.     If ComboBox1.ListIndex = -1 Then Exit Sub
    
15.     Dim t(1 To 2) As Date, Rng As Range
    
16.     With Sheet2
    
17.         Set Rng = .[A:A].Find(ComboBox1, LookAT:=xlWhole)
    
18.         If Rng Is Nothing Then
    
19.             MsgBox "¦WºÙ¿ù»~", vbCritical + vbOKOnly, ""
    
20.             Exit Sub
    
21.         End If
    
22.         If Rng.End(xlDown).Row <> .Rows.Count Then
    
23.             Set Rng = .Range(Rng.Offset(, 2), Rng.End(xlDown).Offset(-1, 2))
    
24.         Else
    
25.             Set Rng = .Range(Rng.Offset(, 2), Rng.Offset(, 2).End(xlDown))
    
26.         End If
    
27.     End With
    
28.     t(1) = Application.Sum(Rng)
    
29.     TextBox1 = Application.Text(t(1), "[hh]:mm")
    
30.     t(2) = Application.Sum(Sheet1.Range("D:D").SpecialCells(xlCellTypeVisible))
    
31.     TextBox2 = Application.Text(t(2), "[hh]:mm")
    
32.     With TextBox3
    
33.         .ForeColor = vbBlack
    
34.         .Text = Application.Text(IIf(t(1) > t(2), t(1) - t(2), t(2) - t(1)), "[hh]:mm")
    
35.         If t(2) > t(1) Then
    
36.             .Text = "-" & .Text
    
37.             .ForeColor = vbRed
    
38.         End If
    
39.      End With
    
40. End Sub
    
41. Private Sub ¦WºÙ¨î©w()
    
42.     Dim E As Range
    
43.     With Sheets("Sheet2").[A:A]  '¨T,¾÷ ÃB«×
    
44.         For Each E In .SpecialCells(xlCellTypeConstants)
    
45.             ComboBox1.AddItem E
    
46.         Next
    
47.     End With
    
48. End Sub
    
49. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
50.     Sheets("Sheet1").AutoFilterMode = False
    
51. End Sub

½Æ»s¥N½X