Excel制作人力資源管理系統，教你如何管理工作

一直以來想開發一個小系統，卻一直沒有行動。人力資源小系統，對于HR專業來說，本人隻是以前做過一段時間的HR，專業并沒有深究，是以涉及的終究是表面的知識，還請各位專業人士海涵。此系統分為7大子產品，那麼由于是用筆記本設計的，可能在分辨率較高的電腦上有東西會錯位，可以自己微調一下就可以！！具體子產品如下所示：

利用ribbon把Excel原有的功能菜單屏蔽了，設定了内容上的六大闆塊；

第一大子產品是基礎資料管理子產品

第二大子產品是合同資料管理子產品

第三大子產品是酬薪資料管理子產品

第四大子產品是教育訓練資料管理子產品

第五大子產品是績效資料管理子產品

第六大子產品是員工關系管理子產品

主要控制代碼也非常簡單：Select Case語句就可以解決。

Sub 主要(control As IRibbonControl)
Select Case control.ID
Case "but1"
UserForm1.Show
Case "but2"
UserForm2.Show
Case "but3"
UserForm3.Show
Case "but4"
UserForm4.Show
Case "but5"
UserForm5.Show
Case "but6"
UserForm6.Show
End Select
End Sub

基礎資料庫代碼：

Private Sub CommandButton1_Click() '随心
  Dim brr(), arr(), k%, rng As Range
  Sheets("基礎資料庫").[f:f].NumberFormatLocal = "@"
  Sheets("基礎資料庫").[d:d].NumberFormatLocal = "yyyy/m/d"
  If TextBox5.Text <> "" Then
  Set rng1 = Sheets("基礎資料庫").[f:f].Find(TextBox5.Text, , , xlWhole)
  If rng1 Is Nothing Then
  For k = 2 To 36
    n = n + 1
    ReDim Preserve brr(1 To n)
    brr(n) = UserForm1.Controls("Textbox" & k).Text
  Next
  Set rng = Sheets("基礎資料庫").Cells(Rows.Count, 1).End(xlUp)(2, 1)
  rng = TextBox1.Text
  
  If OptionButton1.Value = "" Then
  rng(1, 2) = "女"
  Else
  rng(1, 2) = "男"
  End If
  For h = 1 To 4
      If UserForm1.Controls("checkbox" & h).Value Then
      rng(1, 38 + h) = UserForm1.Controls("checkbox" & h).Caption
      End If
  Next
  rng(1, 42) = TextBox37.Text
  rng(1, 43) = TextBox38.Text
  rng(1, 3).Resize(1, UBound(brr)) = Application.Transpose(Application.Transpose(brr))
  MsgBox "儲存成功"
  Else
  MsgBox "此人資訊已在資料庫"
  End If
  Else
  MsgBox "您沒有輸入相關資訊"
  End If
End Sub


Private Sub CommandButton2_Click()
   For i = 1 To 38
       UserForm1.Controls("textbox" & i).Text = ""
   Next
End Sub


Private Sub CommandButton3_Click()
    UserForm1.Hide
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

負責執行具體操作代碼：

Sub Picture1_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("基礎資料庫").Visible = True
    Sheets("基礎資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub


Sub 組合1_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("合同資料庫").Visible = True
    Sheets("合同資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub




Sub 組合2_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("薪酬資料庫").Visible = True
    Sheets("薪酬資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub


Sub 組合3_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("員工教育訓練資料庫").Visible = True
    Sheets("員工教育訓練資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub


Sub 組合5_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("績效福利資料庫").Visible = True
    Sheets("績效福利資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub


Sub 組合6_Click()
    For i = 3 To 1 Step -1
    sr = InputBox("請輸入密碼")
    If sr = "123" Then
    Sheets("員工關系資料庫").Visible = True
    Sheets("員工關系資料庫").Activate
    Exit For
    Else
    MsgBox "您輸入的密碼不正确,請重新輸入密碼" & Chr(13) & "您還可以輸入" & i - 1 & "次密碼"
    End If
    Next
End Sub

合同管理代碼：

Private Sub CommandButton1_Click()
Dim rng1 As Range, rng As Range, rng2 As Range
Sheets("合同資料庫").[A:B].NumberFormatLocal = "@"
Sheets("合同資料庫").[e:f].NumberFormatLocal = "@"
Sheets("合同資料庫").[c:d].NumberFormatLocal = "yyyy/M/d"
Set rng1 = Sheets("合同資料庫").Cells(Rows.Count, 2).End(xlUp)(2, 1)
Set rng = Sheets("合同資料庫").[b:b].Find(UserForm2.TextBox1.Text, , , xlWhole)
Set rng2 = Sheets("基礎資料庫").[f:f].Find(UserForm2.TextBox1.Text, , , xlWhole)
If rng2 Is Nothing Then
MsgBox "基礎資料庫中沒有此人相關資訊，請先增加基礎資訊"
Exit Sub
End If
If rng Is Nothing Then
rng1(1, 0) = rng2(1, -4).Value
rng1.Value = UserForm2.TextBox1.Text
rng1(1, 2) = UserForm2.TextBox2.Text
rng1(1, 3) = UserForm2.TextBox3.Text
rng1(1, 4) = UserForm2.TextBox4.Text
rng1(1, 5) = UserForm2.TextBox5.Text
MsgBox "儲存成功"
Else
MsgBox "您新增的資訊已存在資料庫中"
End If
End Sub


Private Sub CommandButton2_Click()
For i = 1 To 5
       UserForm2.Controls("textbox" & i).Text = ""
   Next
End Sub


Private Sub CommandButton3_Click()
 UserForm2.Hide
End Sub


Private Sub CommandButton4_Click()
    Set rng = Sheets("合同資料庫").[b:b].Find(UserForm2.TextBox1.Text, , , xlWhole)
    If Not rng Is Nothing Then
     rng(1, 2).Value = UserForm2.TextBox2.Text
     rng(1, 3).Value = UserForm2.TextBox3.Text
     rng(1, 4).Value = UserForm2.TextBox4.Text
     rng(1, 5).Value = UserForm2.TextBox5.Text
     MsgBox "已更改資訊"
     Else
     MsgBox "此資訊庫中沒有相關資訊"
     End If
End Sub


Private Sub CommandButton5_Click()
     Set rng = Sheets("合同資料庫").[b:b].Find(UserForm2.TextBox1.Text, , , xlWhole)
     If Not rng Is Nothing Then
            UserForm2.TextBox2.Text = rng(1, 2).Value
            UserForm2.TextBox3.Text = rng(1, 3).Value
            UserForm2.TextBox4.Text = rng(1, 4).Value
            UserForm2.TextBox5.Text = rng(1, 5).Value
        Else
        MsgBox "您輸入的資訊不在資料庫，請增加相關資訊"
     End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

酬薪資料管理代碼：

Private Sub CommandButton1_Click()
Dim rng As Range, rng1 As Range, rng2 As Range
 Sheets("薪酬資料庫").[e:f].NumberFormatLocal = "yyyy/M/d"
Set rng = Sheets("基礎資料庫").[f:f].Find(UserForm3.TextBox1.Text, , , xlWhole)
Set rng1 = Sheets("薪酬資料庫").Cells(Rows.Count, 2).End(xlUp)(2, 1)
Set rng2 = Sheets("薪酬資料庫").[b:b].Find(UserForm3.TextBox1.Text, , , xlWhole)
If Not rng2 Is Nothing Then
MsgBox "此資訊已存在"
Exit Sub
End If
If rng Is Nothing Then
MsgBox "基礎資料庫中沒有此人相關資訊，請新增基礎資訊"
Else
  rng1(1, 0) = rng(1, -4)
  rng1 = UserForm3.TextBox1.Text
  rng1(1, 2) = UserForm3.TextBox2.Text
  rng1(1, 3) = UserForm3.TextBox3.Text
  rng1(1, 4) = UserForm3.TextBox4.Text
  rng1(1, 5) = UserForm3.TextBox5.Text
  MsgBox "儲存成功"
End If
End Sub


Private Sub CommandButton2_Click()
For i = 1 To 5
       UserForm3.Controls("textbox" & i).Text = ""
   Next
End Sub


Private Sub CommandButton3_Click()
 UserForm3.Hide
End Sub


Private Sub CommandButton4_Click()
 Set rng = Sheets("薪酬資料庫").[b:b].Find(UserForm3.TextBox1.Text, , , xlWhole)
    If Not rng Is Nothing Then
     rng(1, 2).Value = UserForm3.TextBox2.Text
     rng(1, 3).Value = UserForm3.TextBox3.Text
     rng(1, 4).Value = UserForm3.TextBox4.Text
     rng(1, 5).Value = UserForm3.TextBox5.Text
     MsgBox "已更改資訊"
     Else
     MsgBox "此資訊庫中沒有相關資訊"
     End If
End Sub


Private Sub CommandButton5_Click()
Dim rng As Range
If UserForm3.TextBox1.Text = "" Then
MsgBox "請輸入身份證号碼"
Exit Sub
End If
Set rng = Sheets("薪酬資料庫").[b:b].Find(UserForm3.TextBox1.Text, , , xlWhole)
If rng Is Nothing Then
   MsgBox "您所查詢的資料不存在，請新增後再查詢"
   Else
   UserForm3.TextBox2.Text = rng(1, 2).Value
   UserForm3.TextBox3.Text = rng(1, 3).Value
   UserForm3.TextBox4.Text = rng(1, 4).Value
   UserForm3.TextBox5.Text = rng(1, 5).Value
   MsgBox "查詢完成"
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

員工管理代碼：

Private Sub CommandButton1_Click()
  Dim rng As Range, rng1 As Range
  Sheets("員工教育訓練資料庫").[c:c].NumberFormatLocal = "@"
  Set rng = Sheets("員工教育訓練資料庫").[c:c].Find(UserForm4.TextBox3.Text, , , xlWhole)
  Set rng1 = Sheets("員工教育訓練資料庫").Cells(Rows.Count, 3).End(xlUp)(2, 1)
  If rng Is Nothing Then
  If UserForm4.TextBox3 = "" Then
  MsgBox "請輸入正确的書籍ID号"
  Exit Sub
  End If
      If rng Is Nothing Then
      rng1 = UserForm4.TextBox3.Text
      rng1(1, 2) = UserForm4.TextBox4.Text
      rng1(1, 0) = UserForm4.TextBox2.Text
      rng1(1, -1) = UserForm4.TextBox1.Text
      MsgBox "新增完成"
      End If
      Else
      MsgBox "此書籍已存在"
      End If
End Sub


Private Sub CommandButton2_Click()
  For i = 1 To 4
  UserForm4.Controls("textbox" & i).Text = ""
  Next
End Sub


Private Sub CommandButton3_Click()
UserForm4.Hide
End Sub


Private Sub CommandButton5_Click()
Set rng = Sheets("員工教育訓練資料庫").[c:c].Find(UserForm4.TextBox3.Text, , , xlWhole)
If Not rng Is Nothing Then
   UserForm4.TextBox1.Text = rng(1, -1)
   UserForm4.TextBox2.Text = rng(1, 0)
   UserForm4.TextBox4.Text = rng(1, 2)
   MsgBox "查詢完成"
Else
MsgBox "資料庫中沒此條記錄"
End If
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

績效資料管理代碼：

Private Sub CommandButton1_Click()
    Dim rng5 As Range, rng6 As Range, rng7 As Range
    Set rng5 = Sheets("基礎資料庫").[f:f].Find(UserForm5.TextBox1.Text, , , xlWhole)
    Set rng6 = Sheets("績效福利資料庫").Cells(Rows.Count, 2).End(xlUp)(2, 1)
    Set rng7 = Sheets("績效福利資料庫").[b:b].Find(UserForm5.TextBox1.Text, , , xlWhole)
    If Not rng7 Is Nothing Then
       If rng7(1, 0) = UserForm5.ComboBox1.Text Then
    MsgBox "此條資訊已儲存在資料庫"
    Exit Sub
    End If
    End If
    If Not rng5 Is Nothing And UserForm5.TextBox1.Text <> "" Then
        rng6 = UserForm5.TextBox1.Text
        rng6(1, 0) = UserForm5.ComboBox1.Text
        rng6(1, 2) = rng5(1, -4)
        If UserForm5.OptionButton1.Value = "" Then
        rng6(1, 3) = "否"
        Else
        rng6(1, 3) = "是"
        End If
        rng6(1, 4) = UserForm5.TextBox2.Text
      MsgBox "新增資訊完成"
     Else
     MsgBox "基礎資料中不存在此條資料"
    End If
End Sub


Private Sub CommandButton2_Click()
For i = 1 To 2
UserForm5.Controls("textbox" & i).Text = ""
Next
UserForm5.ComboBox1.Text = "無"


End Sub


Private Sub CommandButton5_Click()
Dim srrr As Range, srr
srr = UserForm5.ComboBox1.Text & UserForm5.TextBox1.Text
  Set rng7 = Sheets("績效福利資料庫").[f:f].Find(srr, , , xlWhole)
 If Not rng7 Is Nothing Then
    UserForm5.ListBox1.AddItem (rng7(1, -2))
    UserForm5.TextBox2 = rng7(1, 0)
    If rng7(1, 3) = "是" Then
    UserForm5.OptionButton2.Value = ""
    Else
    UserForm5.OptionButton1.Value = ""
    End If
    MsgBox "查詢完成"
 Else
 MsgBox "資料庫中無此條記錄"
 End If
End Sub


Private Sub CommandButton3_Click()
UserForm5.Hide
End Sub


Private Sub UserForm_Initialize()
    Dim srr As Variant
    Dim sr As Variant
    srr = Array("無", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月")
    For Each sr In srr
    UserForm5.ComboBox1.AddItem sr
Next
    UserForm5.ComboBox1.ListRows = 13
    UserForm5.ComboBox1.ListIndex = 0
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

員工關系管理代碼：

Private Sub CommandButton1_Click()
UserForm7.Show
End Sub


Private Sub CommandButton2_Click()
UserForm6.ComboBox1 = ""
UserForm6.TextBox1 = ""
End Sub


Private Sub CommandButton3_Click()
UserForm6.Hide
End Sub


Private Sub CommandButton5_Click()
Dim rng As Range
Set rng = Sheets("員工關系資料庫").[a:a].Find(UserForm6.ComboBox1.Text, , , xlWhole)
UserForm6.TextBox1.Text = rng(1, 2)
End Sub


Private Sub UserForm_Initialize()
 Dim srr As Variant
    Dim sr As Variant
    srr = Array("無", "一、勞動關系管理", "二、員工紀律管理", "三、員勞工際關系管理", "四、溝通管理", "五、員工績效管理", "六、員工情況管理", "七、企業文化建設管理", "八、服務與支援管理", "九、員工關系教育訓練管理")
    For Each sr In srr
    UserForm6.ComboBox1.AddItem sr
Next
    UserForm6.ComboBox1.ListRows = 10
    UserForm6.ComboBox1.ListIndex = 0
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> 1 Then Cancel = True
End Sub

Excel制作人力資源管理系統，教你如何管理工作

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述