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

GBKEE

¦^´_ 3# lifedidi

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

½Æ»s¥N½X

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

GBKEE

¦^´_ 8# lifedidi
½Ð¸Ô¬Ý°Ñ¦Òvbaªº»¡©ú
Format ¨ç¼Æ«h¨Ï¥Î¤£¦P©ó NumberFormat ©M NumberFormatLocal Äݩʪº®æ¦¡¥N½X¦r¦ê¡C

1. NumberFormatLocal ÄÝ©Ê¡C
   
2. ®M¥Î¦Ü Style ª«¥ó®É¥Î NumberFormatLocal ÄÝ©Ê¡C

½Æ»s¥N½X

1. NumberFormat ÄÝ©Ê¡C   
   
2. ®M¥Î¦Ü DataLabel¡BDataLabels¡BPivotField¡BStyle ¤Î TickLabels ª«¥ó®É¥Î NumberFormat ÄÝ©Ê¡C

½Æ»s¥N½X

1. Format ¨ç¼Æ
   
2. ¶Ç¦^¤@ Variant (String)¡A§t¦³¤@­Ó®Ú¾Ú®æ¦¡¹Bºâ¦¡¨Ó®æ¦¡¤Æªº¹Bºâ¦¡¡C

½Æ»s¥N½X

GBKEE

®@~¦³ÂI¤F¸Ñ¤F¡I
©Ò¥HÁÙ¬O­n·Ó¤j¤jªº¤è¦¡¥hRUN¤~¥¿½T¡A
¥i¬O¤j¤j¥i¥Hµy·L»¡©ú¤@¤U§ïÅÜ­þ¸Ì¤~·|Åܮɶ¡®æ¦¡ ...
lifedidi µoªí©ó 2013/4/26 16:30

1. MsgBox Application.Text("1900/1/2", "[hh]")

½Æ»s¥N½X

¦p­nÅã¥Ü¥¿±`ªº®É¶¡®æ¦¡ ¥ÎFormat¨ç¼Æ´N¥i,
¦ý§A­nªº®É¶¡®æ¦¡:¤p®ÉªºÅã¥Ü   [hh]:mm ,Format¨ç¼Æ ¤£¥i¥Î
[hh]:mm ªº®æ¦¡,¦bÀx¦s®æ¤W¥ÎÀx¦s®æ®æ¦¡«ü¥O §Y¥i
TextBox¬OMSForms ªº±±¨î¶µ,¥i¥ÎÀx¦s®æªºValue(¼Æ­È) ©Î Text (®æ¦¡«áªº¦r¦ê)¶Ç»¼

ps:  ¦^¤å®É  ½Ð«ö [¦^ÂÐ] «ö¶s  µªÂЧAªº¤H¤~·|±o¨ì³qª¾

GBKEE

¦^´_ 13# lifedidi
¦bÀx¦sªº®É­Ô³£·|¿ù»~¡A¦p¦ó¿ù»~???

1. Private Sub CommandButton4_Click()
   
2.     With UserForm1
   
3.         If myday <> "" And IsDate(myday) = False Then
   
4.             MsgBox "±z¿é¤Jªº§¹¤u¤é´ÁµLªk¿ë§O", vbCritical + vbOKOnly, "½Ð­«·s¿é¤J"
   
5.             Exit Sub
   
6.         End If
   
7.         .CommandButton2.Enabled = True
   
8.         ³¡ªù = .ListBox1.Text
   
9.         ½s¸¹ = .ListBox2.Text
   
10.         R = d2(³¡ªù & "-" & ½s¸¹)
    
11.         For i = 2 To 4
    
12.             Sheets("SHEET1").Cells(R, i + 1).Value = .Controls("TextBox" & i).Value
    
13.             .Controls("TextBox" & i).ForeColor = vbRed    '¦â±m±`¼Æ(¬õ)
    
14.             .Controls("TextBox" & i).BackColor = vbWhite  '¦â±m±`¼Æ(¥Õ)
    
15.             .Controls("TextBox" & i).Locked = True
    
16.         Next i
    
17.      '   Call UpdateBox             '***³oµ{§Ç¬O¦ó§@¥Î
    
18.       '  .ListBox1.Text = ³¡ªù      '***¬°¦ó­«·sµ¹­È
    
19.        ' .ListBox2.Text = ½s¸¹      '***¬°¦ó­«·sµ¹­È
    
20.         .CommandButton4.Enabled = False
    
21.     End With
    
22.     MsgBox "¤w¸g§¹¦¨Àx¦s", vbOKOnly, "½Ðª`·N"
    
23. End Sub

½Æ»s¥N½X