- 10.社員の基本情報・異動情報の変更、入社・異動・退職を入力する
- (1)異動情報入力フォームを作成する(ユーザーフォーム)
- ①まずは、ユーザーフォームを挿入します。
- ②ユーザーフォームの一行目は社員名を絞り込み検索するための部署名用のボックス(コンボボックス)を4つ配置します。
- ③2行目は、社員番号用のボックス(テキストボックス)と氏名用のボックス(コンボボックス)を配置します。
- ④次に、基本情報と異動情報を入力スペースをマルチページを使って確保します。
- ⑤マルチページの基本情報ページに、各基本情報のボックス(テキストボックス)を配置する。
- ⑥マルチページの「異動情報」ページに、各異動情報のボックス(コンポボックス)を配置する。
- ⑦基本情報や異動情報を「社員基本情報」シートや「異動DB」シートに書き込むプログラムを実行するためのコマンドボタンを配置します。
- ⑧最後に、各テキストボックス、コンボボックスの内容を示す「ラベル」を配置します。
- (2)コンボボックスのアイテム(リスト)を設定する。
- (3)社員氏名の絞り込み検索の設定
- (4)社員氏名のコンボボックスで特定社員を選択したときに、選択した社員の「社員情報」と「異動情報」の内容を読み込むコードを記入します。
- (5)社員基本情報に変更があるとき、「社員基本情報」シートの内容を変更するコードを記入する。
- (6)社員異動情報に変更があるとき「社員DB」シートの内容を変更するコードを記入する。
- (7)社員退職時に退職情報を「異動DBシート」に入力するコードを記入する。
- (8)最後にUserForm2を起動するコマンドボタンをmenuシートに配置します
- (1)異動情報入力フォームを作成する(ユーザーフォーム)
- 11.人事システムの操作方法
10.社員の基本情報・異動情報の変更、入社・異動・退職を入力する
社員の異動情報の入力フォームもユーザーフォームを活用します。所属部署を選択して社員を絞り込み、絞り込んだ中から特定の社員を選択すると、当該社員の現在の情報を全て読み込みます。読み込んだ情報のうち、変更箇所を変更すれば、データベースに反映される仕組みを作っていきます。
(1)異動情報入力フォームを作成する(ユーザーフォーム)
①まずは、ユーザーフォームを挿入します。
「開発」タグ → 「Visual Basic」
「Visual Basic」の「挿入」タグ → 「ユーザーフォーム」を選択します。
②ユーザーフォームの一行目は社員名を絞り込み検索するための部署名用のボックス(コンボボックス)を4つ配置します。
開いた「UserForm2」の右下の角をドラッグして大きさを調整します。
「表示」タグ → 「ツールボックス」を選択して、ツールボックスを表示させます。
「ツールボックス」の「コンボボックス」を選択して、ユーザーフォーム上で適当な大きさにドラッグして配置します。
同様にして、合計4つコンボボックスを並べます。
③2行目は、社員番号用のボックス(テキストボックス)と氏名用のボックス(コンボボックス)を配置します。
「ツールボックス」の「テキストボックス」を選択して、ユーザーフォーム上で適当な大きさにドラッグして配置します。
同じく「ツールボックス」の「コンボボックス」を選択して、ユーザーフォーム上に配置します。
④次に、基本情報と異動情報を入力スペースをマルチページを使って確保します。
「ツールボックス」の「マルチページ」を選択して、ユーザーフォーム上で適当な大きさにドラッグして配置します。
マルチページの1目のタグに「基本情報」、2つ目のタグに「異動情報」と入力します。
⑤マルチページの基本情報ページに、各基本情報のボックス(テキストボックス)を配置する。
まず、マルチページの「基本情報」のタグを選択します。
次に、「ツールボックス」の「テキストボックス」を選択して、マルチページの「基本情報」ページ上で適当な大きさにドラッグしてテキストボックスを配置します。
同様にして、計15個(「性別」「生年月日」「年齢」「血液型」「入社年月日」「勤続年数」「郵便番号」「住所」「電話番号」「携帯番号」「メールアドレス」「最終学歴」「健康保険番号」「年金番号」「基礎年金番号」)のテキストボックスを配置します。
⑥マルチページの「異動情報」ページに、各異動情報のボックス(コンポボックス)を配置する。
まず、マルチページの「異動情報」のタグを選択します。
次に、「ツールボックス」の「コンボボックス」を選択して、マルチページの「異動情報」ページ上で適当な大きさにドラッグしてコンボボックスを配置します。
同様にして、計9個(「本部」「部」「課」「係」「雇用形態」「職掌」「格付1」「格付2」「役職」)のコンボボックスを配置します。
⑦基本情報や異動情報を「社員基本情報」シートや「異動DB」シートに書き込むプログラムを実行するためのコマンドボタンを配置します。
ⅰ まず「マルチページ」の「基本情報」ページ上に1つ配置します。
「ツールボックス」の「コマンドボタン」を選択して、マルチページの「基本情報」ページ上で適当な大きさにドラッグしてコマンドボタンを配置しボタンの表示を「更新」に変更します。
ⅱ 次に、マルチページの「異動情報」ページ上にも同様に1つ「コマンドボタン」を配置しボタンの表示を「更新」に変更します。
ⅲ 最後に、ユーザーフォームの右上に、コマンドボタンを1つ配置しボタンの表示を「退職」に変更します。
⑧最後に、各テキストボックス、コンボボックスの内容を示す「ラベル」を配置します。
ラベルは「ツールボックス」の「ラベル」を選択し、適当な大きさにドラッグして配置し、表示名を入力することで作成できます。これを全てのボックスについて、行います。
(2)コンボボックスのアイテム(リスト)を設定する。
以上で、ユーザーフォームのボックスとボタンの配置は完了しました。次に、コードを入力していきます。
まずは、コンボボックスのアイテムリストを設定するコードを記入します。
UserForm2モジュールを開きます。
下記コードを「UserForm2モジュール」に記入(コピペ)します。
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.AddItem Worksheets("組織マスター").Range("a" & i).Value
ComboBox6.AddItem Worksheets("組織マスター").Range("a" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 3).End(xlUp).Row
ComboBox2.AddItem Worksheets("組織マスター").Range("c" & i).Value
ComboBox7.AddItem Worksheets("組織マスター").Range("c" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 5).End(xlUp).Row
ComboBox3.AddItem Worksheets("組織マスター").Range("e" & i).Value
ComboBox8.AddItem Worksheets("組織マスター").Range("e" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 7).End(xlUp).Row
ComboBox4.AddItem Worksheets("組織マスター").Range("g" & i).Value
ComboBox9.AddItem Worksheets("組織マスター").Range("g" & i).Value
Next i
For i = 3 To Worksheets("現在の社員名簿").Cells(Rows.Count, 16).End(xlUp).Row
ComboBox5.AddItem Worksheets("現在の社員名簿").Range("p" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 10).End(xlUp).Row
ComboBox10.AddItem Worksheets("組織マスター").Range("j" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 12).End(xlUp).Row
ComboBox11.AddItem Worksheets("組織マスター").Range("l" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 13).End(xlUp).Row
ComboBox12.AddItem Worksheets("組織マスター").Range("m" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 15).End(xlUp).Row
ComboBox13.AddItem Worksheets("組織マスター").Range("o" & i).Value
Next i
For i = 2 To Worksheets("組織マスター").Cells(Rows.Count, 17).End(xlUp).Row
ComboBox14.AddItem Worksheets("組織マスター").Range("q" & i).Value
Next i
End Sub(3)社員氏名の絞り込み検索の設定
「本部」「部」「課」「係」を選択すると、該当する社員氏名のアイテムリストが絞り込まれるように設定します。
やり方としては、Excelのフィルター機能をマクロで動かします。
①検索シートの作成
まずは、検索用のシートを作成し、シート名を「検索」に変更します。
次に、「検索」シートの1行目と、4行目のA列からG列に、「社員番号」「本部」「部」「課」「係」「役職」「氏名」とそれぞれ入力します。
②検索用のコードを記入する。
ⅰ まず「UserForm2モジュール」に下記コードを記入します。
Private Sub ComboBox1_Change()
Worksheets("検索").Cells(2, 2) = ComboBox1.Value
Call Module3.kensaku
End Sub
Private Sub ComboBox2_Change()
Worksheets("検索").Cells(2, 3) = ComboBox2.Value
Call Module3.kensaku
End Sub
Private Sub ComboBox3_Change()
Worksheets("検索").Cells(2, 4) = ComboBox3.Value
Call Module3.kensaku
End Sub
Private Sub ComboBox4_Change()
Worksheets("検索").Cells(2, 5) = ComboBox4.Value
Call Module3.kensaku
End Subⅱ 次に、標準モジュール(Module3)を挿入します。
「VisualBasic」の「挿入」タグから「標準モジュール」を選択します。
ⅲ 「標準モジュール(Module3)」に下記コードを記入します。
Sub kensaku()
Dim i As Integer
i = Worksheets("現在の社員名簿").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("現在の社員名簿").Range("A2:AE" & i).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("検索").Range("A1:G2"), CopyToRange:=Worksheets("検索").Range("A4:G4"), Unique:=False
UserForm2.ComboBox5.Object.Clear
For i = 5 To Worksheets("検索").Cells(Rows.Count, 7).End(xlUp).Row
UserForm2.ComboBox5.AddItem Worksheets("検索").Range("g" & i).Value
Next i
End Sub(4)社員氏名のコンボボックスで特定社員を選択したときに、選択した社員の「社員情報」と「異動情報」の内容を読み込むコードを記入します。
「UserForm2モジュール」に下記コードを記入します。
Private Sub ComboBox5_Change()
For i = 1 To 16
Me.Controls("TextBox" & i).Value = ""
Next i
For i = 6 To 14
Me.Controls("ComboBox" & i).Value = ""
Next i
Dim rcd As Range, simei As String
simei = ComboBox5.Value
Set rcd = Worksheets("現在の社員名簿").Range("p:p").Find(simei, lookat:=xlWhole)
If Not rcd Is Nothing Then
TextBox1 = rcd.Offset(0, -14)
For i = 2 To 16
Me.Controls("TextBox" & i).Value = rcd.Offset(0, i - 1)
Next i
ComboBox6.Value = rcd.Offset(0, -13)
ComboBox7.Value = rcd.Offset(0, -12)
ComboBox8.Value = rcd.Offset(0, -11)
ComboBox9.Value = rcd.Offset(0, -10)
ComboBox10.Value = rcd.Offset(0, -8)
ComboBox11.Value = rcd.Offset(0, -6)
ComboBox12.Value = rcd.Offset(0, -5)
ComboBox13.Value = rcd.Offset(0, -4)
ComboBox14.Value = rcd.Offset(0, -2)
End If
End Sub(5)社員基本情報に変更があるとき、「社員基本情報」シートの内容を変更するコードを記入する。
「UserForm2モジュール」に下記コードを記入します。
Private Sub CommandButton1_Click()
Dim rcd As Range, syain_no As Integer
syain_no = Val(TextBox1.Text)
If syain_no = 0 Then
MsgBox "社員番号を入力してください"
Exit Sub
End If
Set rcd = Worksheets("社員基本情報").Range("a:a").Find(syain_no, lookat:=xlWhole)
If rcd Is Nothing Then
Dim rc As VbMsgBoxResult
Dim n As Integer, i As Integer
rc = MsgBox("新規社員情報を追加しますか？", vbYesNo + vbQuestion)
If rc = vbYes Then
With Worksheets("社員基本情報")
n = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(n, 1) = TextBox1.Value
.Cells(n, 2) = ComboBox5.Value
.Cells(n, 3) = TextBox2.Value
.Cells(n, 4) = TextBox3.Value
.Cells(n, 5) = TextBox5.Value
.Cells(n, 6) = TextBox6.Value
.Cells(n, 7) = TextBox8.Value
.Cells(n, 8) = TextBox9.Value
.Cells(n, 9) = TextBox10.Value
.Cells(n, 10) = TextBox11.Value
.Cells(n, 11) = TextBox12.Value
.Cells(n, 12) = TextBox13.Value
.Cells(n, 13) = TextBox14.Value
.Cells(n, 14) = TextBox15.Value
.Cells(n, 15) = TextBox16.Value
End With
MsgBox "社員基本情報に" & ComboBox5.Value & "さん を追加しました", vbInformation
Else
Exit Sub
End If
Else
rc = MsgBox("社員基本情報を更新しますか？", vbYesNo + vbQuestion)
If rc = vbYes Then
rcd.Offset(0, 1) = ComboBox5.Value
rcd.Offset(0, 2) = TextBox2.Value
rcd.Offset(0, 3) = TextBox3.Value
rcd.Offset(0, 4) = TextBox5.Value
rcd.Offset(0, 5) = TextBox6.Value
rcd.Offset(0, 6) = TextBox8.Value
rcd.Offset(0, 7) = TextBox9.Value
rcd.Offset(0, 8) = TextBox10.Value
rcd.Offset(0, 9) = TextBox11.Value
rcd.Offset(0, 10) = TextBox12.Value
rcd.Offset(0, 11) = TextBox13.Value
rcd.Offset(0, 12) = TextBox14.Value
rcd.Offset(0, 13) = TextBox15.Value
rcd.Offset(0, 14) = TextBox16.Value
MsgBox ComboBox5.Value & "さんの基本情報を更新しました", vbInformation
Else
Exit Sub
End If
End If
End Sub(6)社員異動情報に変更があるとき「社員DB」シートの内容を変更するコードを記入する。
「UserForm2モジュール」に下記コードを記入します。
Private Sub CommandButton2_Click()
Dim rcd As Range, syain_no As Integer, m As Date
syain_no = Val(TextBox1.Text)
If syain_no = 0 Then
MsgBox "社員番号を入力してください"
Exit Sub
End If
m = InputBox("異動日を入力してください")
Set rcd = Worksheets("現在の社員名簿").Range("b:b").Find(syain_no, lookat:=xlWhole)
If rcd Is Nothing Then
Dim rc As VbMsgBoxResult
Dim n As Integer, i As Integer
rc = MsgBox("新規社員情報を追加しますか？", vbYesNo + vbQuestion)
If rc = vbYes Then
With Worksheets("異動DB")
n = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(n, 1) = 1
.Cells(n, 2) = m
.Cells(n, 3) = ""
.Cells(n, 4) = syain_no
For i = 5 To 14
.Cells(n, i) = Me.Controls("ComboBox" & i).Value
Next i
End With
MsgBox "社員基本情報に" & ComboBox5.Value & "さん を追加しました", vbInformation
Else
Exit Sub
End If
Else
rc = MsgBox("社員基本情報を更新しますか？", vbYesNo + vbQuestion)
If rc = vbYes Then
Dim no As Integer, rcd_no As Range
no = rcd.Offset(0, -1)
If Worksheets("異動DB").Cells(no, 3) = "" Then
Worksheets("異動DB").Cells(no, 3) = m - 1
With Worksheets("異動DB")
n = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(n, 1) = 2
.Cells(n, 2) = m
.Cells(n, 3) = ""
.Cells(n, 4) = syain_no
For i = 5 To 14
.Cells(n, i) = Me.Controls("ComboBox" & i).Value
Next i
End With
Else
MsgBox "すでに異動したデータです。更新できません。「異動DB」で確認下さい"
Exit Sub
End If
Else
Exit Sub
End If
End If
End Sub(7)社員退職時に退職情報を「異動DBシート」に入力するコードを記入する。
「UserForm2モジュール」に下記コードを記入します。
Private Sub CommandButton3_Click()
Dim rcd As Range, syain_no As Integer, m As Date
syain_no = Val(TextBox1.Text)
m = InputBox("異動日を入力してください")
Set rcd = Worksheets("現在の社員名簿").Range("b:b").Find(syain_no, lookat:=xlWhole)
If Not rcd Is Nothing Then
Dim rc As VbMsgBoxResult
Dim n As Integer, i As Integer
rc = MsgBox(m & ComboBox5.Value & "さん 退職で更新しますか？", vbYesNo + vbQuestion)
If rc = vbYes Then
Dim no As Integer, rcd_no As Range
no = rcd.Offset(0, -1)
If Worksheets("異動DB").Cells(no, 3) = "" Then
Worksheets("異動DB").Cells(no, 3) = m
With Worksheets("異動DB")
n = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(n, 1) = 3
.Cells(n, 2) = m
.Cells(n, 3) = m
.Cells(n, 4) = syain_no
For i = 5 To 14
.Cells(n, i) = Me.Controls("ComboBox" & i).Value
Next i
End With
Else
MsgBox "すでに異動したデータです。更新できません。「異動DB」で確認下さい"
Exit Sub
End If
Else
Exit Sub
End If
End If
End Sub(8)最後にUserForm2を起動するコマンドボタンをmenuシートに配置します
「開発」タグ →「挿入」→ActiveXコントロールの「コマンドボタン」を選択
menuシート上の適当な場所にコマンドボタンを配置して、表示名を変更する。
Visual Basicの「menu」シートモジュールに下記コードを記入する。
Private Sub CommandButton2_Click()
UserForm2.Show vbModeless
End Sub11.人事システムの操作方法
人事システムの操作方法を簡単な動画にまとめました。
コメント