- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
69#
發表於 2016-9-6 10:50
| 只看該作者
本帖最後由 c_c_lai 於 2016-9-6 16:47 編輯
回復 67# v03586
EX: 修正提案 by 2016/9/5 v03586
假設 1ST 總人力 75人 V 組 25人 , P 組 23人 , K組 26人。
今天離職一位 1ST K 組人員, 點選人員回報功能鍵後；
總人力應該由 75人變更為 74, K 組應同時調整異動為 25人。
表單 (DA編輯人員):- Option Explicit
- Dim 復職旗標 As Boolean ' 如該員業已離職，而於事後又申請復職
- Private Sub 異動班別_Click()
- If 異動班別.Value = True Then
- 異動_班別.Enabled = True
- Else
- 異動_班別.Enabled = False
- End If
- End Sub
- Private Sub 異動所屬領班_Click()
- If 異動所屬領班.Value = True Then
- 異動領班.Enabled = True
- Else
- 異動領班.Enabled = False
- End If
- End Sub
- Private Sub 專長_Change()
- Dim ar As Variant, sp As Variant
-
- ar = Array("DA", "Sub", "EC", "PL", "MH", "PT", "代理人")
- 專長1.Clear
- 專長2.Clear
-
- For Each sp In ar
- If sp <> 專長 Then 專長1.AddItem sp
- Next
- End Sub
- Private Sub 班別_Change()
- 領班.Clear
-
- If 班別 = "1ST" Then ' 1ST 領班
- 領班.AddItem "宋小小"
- 領班.AddItem "林小小"
- End If
-
- If 班別 = "2ND" Then ' 2ND 領班
- 領班.AddItem "蔡小小"
- 領班.AddItem "洪小小"
- End If
-
- If 班別 = "3RD" Then ' 3RD 領班
- 領班.AddItem "顏小小"
- 領班.AddItem "林中小"
- End If
- End Sub
- Private Sub 專長1_Change()
- Dim ar As Variant, sp As Variant
-
- ar = Array("DA", "Sub", "EC", "PL", "MH", "PT", "代理人")
- 專長2.Clear
-
- For Each sp In ar
- If sp <> 專長 And sp <> 專長1 Then 專長2.AddItem sp
- Next
- End Sub
- Private Sub 異動_班別_Change()
- 異動領班.Clear
-
- If 異動_班別 = "1ST" Then ' 異動 1ST 領班
- 異動領班.AddItem "宋小小"
- 異動領班.AddItem "林小小"
- End If
-
- If 異動_班別 = "2ND" Then ' 異動 2ND 領班
- 異動領班.AddItem "蔡小小"
- 異動領班.AddItem "洪小小"
- End If
-
- If 異動_班別 = "3RD" Then ' 異動 3RD 領班
- 異動領班.AddItem "顏小小"
- 異動領班.AddItem "林中小"
- End If
- End Sub
- Private Sub 復職_Click()
- If Len(人員工號.Text) = 4 And 復職 Then 填入資料
- End Sub
- Private Sub 確認_Click()
- Dim Rng As Range, sh As Range, i As Integer
-
- With Worksheets("人力資料庫")
- Set Rng = .Range("C:C").Find(人員工號.Value, LookIn:=xlValues, LookAt:=xlWhole)
- If Not Rng Is Nothing Then ' 人員工號已存在
- If 異動班別.Value = True And (異動_班別.Value = "" Or 異動_班別.Value = "請選擇") Then
- MsgBox "勾選異動班別，請選擇要異動的班別!"
- Exit Sub
- ElseIf 異動班別.Value = True Then
- .Cells(Rng.Row, 1) = 異動_班別.Value
- ElseIf 異動班別.Value = False Then
- .Cells(Rng.Row, 1) = 班別.Value
- End If
-
- If 異動所屬領班.Value = True And (異動_班別.Value = "" Or 異動_班別.Value = "請選擇") Then
- MsgBox "勾選異動異動所屬領班，請選擇要異動的領班!"
- Exit Sub
- ElseIf 異動所屬領班.Value = True Then
- .Cells(Rng.Row, 2) = 異動領班.Value
- ElseIf 異動所屬領班.Value = False Then
- .Cells(Rng.Row, 2) = 領班.Value
- End If
-
- .Cells(Rng.Row, 3) = 人員工號.Value ' 工號
- .Cells(Rng.Row, 4) = 姓名.Value ' 姓名
-
- If V.Value = True Then
- .Cells(Rng.Row, 5) = "V"
- ElseIf P.Value = True Then
- .Cells(Rng.Row, 5) = "P"
- ElseIf K.Value = True Then
- .Cells(Rng.Row, 5) = "K"
- End If
-
- .Cells(Rng.Row, 6) = 到職日.Value ' 到職日
- .Cells(Rng.Row, 7) = 專長.Value ' 主要專長
- .Cells(Rng.Row, 8) = 專長1.Value ' 專長1
- .Cells(Rng.Row, 9) = 專長2.Value ' 專長2
-
- Set sh = Worksheets("人員回報").[D4]
- If .Cells(Rng.Row, 1) = "2ND" Then ' 班別
- Set sh = sh.Offset(23) ' = [D27]
- ElseIf .Cells(Rng.Row, 1) = "3RD" Then
- Set sh = sh.Offset(46) ' = [D50]
- End If
- If 離職 = True Then
- ' Rows(rng.Row & ":" & rng.Row).Select
- ' Selection.Delete Shift:=xlUp
- ' Sheets("操作介面").Select
- ' 員工請辭後，其員工資料暫不予以刪除；只在「備註欄」內記載「離職」事實。
- .Cells(Rng.Row, 10) = "該員已於 " & Format(Date, "YY/MM/DD") & " 離職"
- sh.Offset(1, 1) = sh.Offset(1, 1) - 1
- i = Application.Match(UCase(.Cells(Rng.Row, 5)), Array("V", "P", "K"), 0) ' **歸納組別
- sh.Offset(i) = sh.Offset(i) - 1
- Else
- If 復職旗標 = True Then
- sh.Offset(1, 1) = sh.Offset(1, 1) + 1
- i = Application.Match(UCase(.Cells(Rng.Row, 5)), Array("V", "P", "K"), 0) ' **歸納組別
- sh.Offset(i) = sh.Offset(i) + 1
- 復職旗標 = False
- End If
-
- .Cells(Rng.Row, 10) = 備註.Value ' Remark
- End If
- End If
- End With
-
- Call 清空_Click
- MsgBox "資料異動完成"
- End Sub
- Private Sub 清空_Click()
- 到職日.Value = ""
- 備註.Value = ""
- 人員工號.Value = ""
- 姓名.Value = ""
- 班別.Value = ""
- 領班.Value = ""
- 專長.Value = ""
- 專長1.Value = ""
- 專長2.Value = ""
- 離職.Value = False
- 人員工號.Enabled = True
- 確認.Enabled = False
- End Sub
- Private Sub 取消_Click()
- Unload Me
- End Sub
- Private Sub 填入資料()
- Dim Rng As Range
-
- 復職旗標 = False
-
- With Worksheets("人力資料庫")
- Set Rng = .Range("C:C").Find(人員工號.Value, LookIn:=xlValues, LookAt:=xlWhole)
- If Not Rng Is Nothing Then ' 人員工號已存在
- If InStr(.Cells(Rng.Row, 10), "離職") > 0 And 復職 = False Then ' Remark
- 姓名.Value = .Cells(Rng.Row, 4) ' 姓名
- MsgBox .Cells(Rng.Row, 10)
- 復職.Enabled = True
- 姓名.Value = ""
- Exit Sub
- End If
- If 復職 Then .Cells(Rng.Row, 10) = "該員已於 " & Format(Date, "YY/MM/DD") & " 復職": 復職旗標 = True
- 備註.Value = .Cells(Rng.Row, 10) ' Remark
-
- 復職 = False
- 復職.Enabled = False
- 離職 = False
-
- 班別.Value = .Cells(Rng.Row, 1) ' 班別
- 領班.Value = .Cells(Rng.Row, 2) ' 領班
- 人員工號.Value = .Cells(Rng.Row, 3) ' 工號
- 姓名.Value = .Cells(Rng.Row, 4) ' 姓名
-
- If .Cells(Rng.Row, 5) = "V" Then
- V.Value = True
- ElseIf .Cells(Rng.Row, 5) = "P" Then
- P.Value = True
- ElseIf .Cells(Rng.Row, 5) = "K" Then
- K.Value = True
- End If
-
- 到職日.Value = .Cells(Rng.Row, 6) ' 到職日
- 專長.Value = .Cells(Rng.Row, 7) ' 主要專長
- 專長1.Value = .Cells(Rng.Row, 8) ' 專長1
- 專長2.Value = .Cells(Rng.Row, 9) ' 專長2
- 人員工號.Enabled = False
- 確認.Enabled = True
- ' MsgBox "資料查詢完成"
- Else
- 復職 = False
- 復職.Enabled = False
- MsgBox "查無該員工資料"
- End If
- End With
- End Sub
- Private Sub 人員工號_Change()
- 人員工號.Text = UCase(人員工號.Text)
- If Len(人員工號.Text) = 4 Then 填入資料
- End Sub
- Private Sub UserForm_Initialize()
- 班別.AddItem "1ST"
- 班別.AddItem "2ND"
- 班別.AddItem "3RD"
-
- 異動_班別.AddItem "1ST"
- 異動_班別.AddItem "2ND"
- 異動_班別.AddItem "3RD"
-
- 專長.AddItem "DA"
- 專長.AddItem "Sub"
- 專長.AddItem "EC"
- 專長.AddItem "PL"
- 專長.AddItem "MH"
- 專長.AddItem "PT"
- 專長.AddItem "代理人"
- 復職.Enabled = False
- 復職旗標 = False
- End Sub
|
|
發表於 2016-9-3 14:44
| 
