ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¾lÃB+textbox®æ¦¡

[µo°Ý] ¾lÃB+textbox®æ¦¡

¤j¤j¦n¡G

1. ¤p§Ì¦³¤T­ÓSHEET¡A­n¹ï·Ó¸ê®Æ(®É¶¡®æ¦¡)¦AUSERFORM¸ÌªºTEXTBOX¸Ì¡A
¹J¨ì´X­Ó§xÃø¡A½Ð¬Ýªþ¥óÀÉ¡C
2. ¦btextbox­nKEY¤J®É¶¡(®æ¦¡¬°[hh]:mm)½Ð°Ý¸Ó«ç»ò³]©w©O¡H

Á|¨Ò 43:52(43¤p®É:52¤À)¡Fµ²ªGkey¤J«áÅÜ1.82777777777778

ÃB«×°ÝÃD.rar (57.01 KB)

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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~

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-4-26 11:00 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# lifedidi
  1. TextBox1 = ActiveSheet.Range("D1").Text
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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)

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

½Ð°Ý³o¨â­Ó¸ê®ÆªºÅÜ´«¡A¼vÅT¤F­þ¨Ç©O¡H
§Ú¥H¬°¬O¦btextbox«á­±¥[­Ófromate(xxx)¤§ÃþªºÂ²³æªºµ{¦¡½X=.=

TOP

¥»©«³Ì«á¥Ñ lifedidi ©ó 2013-4-26 15:53 ½s¿è

¤j¤j¦n¡I
§Ú¦bPrivate Sub ListBox2_Change()³Ì«á­±¥[¤W¥H¤U½s½X
yy= TextBox4.Value
If TextBox4.Value <> "" Then TextBox4.Value = Format(yy, "hhh:mm")

µ²ªG18:00Åܦ¨1818:00
°²¦p§ï¦¨500:15Åܦ¨2020:15

«Ü¹³¬O¶W¹L24h·|¶i¦ìªº¼Ë¤l¡A½Ð°Ý¦p¦ó¤£¶i¦ì©O¡H

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD