- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 1# lifedidi - Private Sub UserForm_Initialize() 'û¤uÃB«×ªí³æ
- ¦WºÙ¨î©w
- End Sub
- Private Sub ComboBox1_Change()
- With Sheets("Sheet1")
- .AutoFilterMode = False
- .Range("A1").AutoFilter FIELD:=3, Criteria1:=ComboBox1
- TextBox1 = ""
- TextBox2 = ""
- TextBox3 = ""
- End With
- End Sub
- Private Sub CommandButton1_Click() '¬d¸ß¶s
- If ComboBox1.ListIndex = -1 Then Exit Sub
- Dim t(1 To 2) As Date, Rng As Range
- Set Rng = Sheet2.[B:B].Find(ComboBox1, LookAT:=xlWhole)
- If Rng Is Nothing Then
- MsgBox "¦WºÙ¿ù»~", vbCritical + vbOKOnly, ""
- Exit Sub
- End If
- t(1) = Rng.Offset(, 1)
- TextBox1 = Application.Text(t(1), "[hh]:mm")
- t(2) = Application.Sum(Sheet1.Range("D:D").SpecialCells(xlCellTypeVisible))
- TextBox2 = Application.Text(t(2), "[hh]:mm")
- With TextBox3
- .ForeColor = vbBlack
- .Text = Application.Text(IIf(t(1) > t(2), t(1) - t(2), t(2) - t(1)), "[hh]:mm")
- If t(2) > t(1) Then
- .Text = "-" & .Text
- .ForeColor = vbRed
- End If
- End With
- End Sub
- Private Sub ¦WºÙ¨î©w()
- With Sheets("SHEET1")
- .Cells(1, .Columns.Count).EntireColumn.Clear
- .[A1].CurrentRegion.Columns(3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
- ComboBox1.List = .Range(.Cells(2, .Columns.Count), .Cells(2, .Columns.Count).End(xlDown)).Value
- End With
- End Sub
- Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
- Sheets("Sheet1").AutoFilterMode = False
- End Sub
½Æ»s¥N½X- Private Sub UserForm_Initialize() '¤u¨ãÃB«×ªí³æ
- ¦WºÙ¨î©w
- End Sub
- Private Sub ComboBox1_Change()
- With Sheets("Sheet1")
- .AutoFilterMode = False
- .Range("A1").AutoFilter FIELD:=2, Criteria1:=ComboBox1
- TextBox1 = ""
- TextBox2 = ""
- TextBox3 = ""
- End With
- End Sub
- Private Sub CommandButton1_Click() '¬d¸ß¶s
- If ComboBox1.ListIndex = -1 Then Exit Sub
- Dim t(1 To 2) As Date, Rng As Range
- With Sheet2
- Set Rng = .[A:A].Find(ComboBox1, LookAT:=xlWhole)
- If Rng Is Nothing Then
- MsgBox "¦WºÙ¿ù»~", vbCritical + vbOKOnly, ""
- Exit Sub
- End If
- If Rng.End(xlDown).Row <> .Rows.Count Then
- Set Rng = .Range(Rng.Offset(, 2), Rng.End(xlDown).Offset(-1, 2))
- Else
- Set Rng = .Range(Rng.Offset(, 2), Rng.Offset(, 2).End(xlDown))
- End If
- End With
- t(1) = Application.Sum(Rng)
- TextBox1 = Application.Text(t(1), "[hh]:mm")
- t(2) = Application.Sum(Sheet1.Range("D:D").SpecialCells(xlCellTypeVisible))
- TextBox2 = Application.Text(t(2), "[hh]:mm")
- With TextBox3
- .ForeColor = vbBlack
- .Text = Application.Text(IIf(t(1) > t(2), t(1) - t(2), t(2) - t(1)), "[hh]:mm")
- If t(2) > t(1) Then
- .Text = "-" & .Text
- .ForeColor = vbRed
- End If
- End With
- End Sub
- Private Sub ¦WºÙ¨î©w()
- Dim E As Range
- With Sheets("Sheet2").[A:A] '¨T,¾÷ ÃB«×
- For Each E In .SpecialCells(xlCellTypeConstants)
- ComboBox1.AddItem E
- Next
- End With
- End Sub
- Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
- Sheets("Sheet1").AutoFilterMode = False
- End Sub
½Æ»s¥N½X |
|