- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
本帖最後由 GBKEE 於 2014-8-28 05:06 編輯
回復 7# 小華 - Workbooks.Open Filename:=y.Range("L20") '開啟資料庫
- Private Sub Enter_Click()
- Dim Rng As Range, The_allTime As Single, AddTime As Single, Msg As Boolean
- If ComboBox1 <> "" And Year01 <> "" And Month01 <> "" Then
- With Sheets("DataBasa")
- Set Rng = .Range("a6").CurrentRegion '資料庫
- '*******************設資料庫準則
- .Cells(1, .Columns.Count) = "日期條件" '條件式準則欄位名稱,須與資料庫的欄位名稱不相同
- .Cells(2, .Columns.Count) = "=AND(YEAR(日期)=" & Year01 & ",MONTH(日期)=" & Month01 & ")"
-
- .Cells(1, .Columns.Count - 1) = "姓名" ' "總工時"
- .Cells(2, .Columns.Count - 1) = ComboBox1
-
- The_allTime = Application.DSum(Rng, "總工時", .Cells(1, .Columns.Count - 1).Resize(2, 2))
- 'Application.DSum 資料庫函數(H0 參數:=資料庫,參數:=欄位,參數:=準則條件) 加總
- If The_allTime > 30 Then
- Msg = True
- MsgBox ComboBox1 & " " & Year01 & "/" & Month01 & "加班時數 " & The_allTime
-
- ElseIf ComboBox6 <> "" And ComboBox5 <> "" Then
- AddTime = (TimeValue(ComboBox6) - TimeValue(ComboBox5)) * 24 '新增加班時數
- If The_allTime + AddTime > 30 Then '以加班時數+新憎班時數
- Msg = True
- MsgBox ComboBox1 & " " & Year01 & "/" & Month01 & "加班時數 " & The_allTime & " + " & AddTime
- End If
- End If
- End With
- End If
- If Msg Then Exit Sub '有超過30小時,離開程式
- Set w = Sheets("DataBasa")
- Set Y = Sheets("Base")
- AAAA = ""
- '你繼續下去的程式碼
|
|
發表於 2014-8-27 07:26
| 