Option Explicit
'社員別社会保険料設定フォーム

Private mobjWS() As Worksheet       'ワークシート
'mobjWS(0):Sheet4:社会保険料データ
'mobjWS(1):Sheet2:社員情報
'mobjWS(2):Sheet1:基本情報

Private mlngFID As Long             '外部キー
Private mlngRow As Long             'レコード
Private mintKeitai As Integer       '雇用形態区分；「０」取締役；「１」一般；「２」パート；「３」アルバイト
Private mintZaisyoku As Integer     '在職退職区分；「０」在職；「１」死亡退職；「２」普通退職


'閉じるボタン
Private Sub cmdEND_Click()

    Unload Me
    
End Sub

'登録ボタン
Private Sub cndOK_Click()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim intMsg As Integer           'メッセージ
    Dim i As Integer                '整数型カウンタ
    
    intMsg = MsgBox("登録しますか。", 32 + 4, "給与計算システム")
    
    If intMsg = 6 Then
        'NULL値の回避
        For i = 1 To 15
            Set objText = Controls("txtKingaku" & CStr(i))
            If objText.Text = "" Then objText.Text = 0
            If IsNumeric(objText.Text) = False Then GoTo ErrorProc
        Next
        'ワークシートに登録
        Call LetData
        'コントロールリフレッシュ
        Call NewData
    End If
    
    'オブジェクトの開放
    Set objText = Nothing
    Exit Sub
    
ErrorProc:

    MsgBox "入力値が不正です。", 16 + 0, "給与計算システム"
    
End Sub

'社員リストを選択
Private Sub lstSyain_Click()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim lngFID As Long              '外部キー
    Dim i As Integer                '整数型カウンタ
    
    '新規レコードの再定義
    mlngRow = mobjWS(0).Cells(65536, 1).End(xlUp).Row
    
    '外部（社員ID）キーの取得
    mlngFID = Me.lstSyain.List(Me.lstSyain.ListIndex, 0)
    'ワークシート値の取得
    Call GetData
    'コントロールのアクセス許可
    For i = 1 To 15
        Set objText = Controls("txtKingaku" & CStr(i))
        If objText.Enabled = False Then objText.Enabled = True
    Next
    
    'オブジェクトの開放
    Set objText = Nothing
    
End Sub

'フォーカスがあたったとき
Private Sub txtKingaku1_Enter()

    With Me.txtKingaku1
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With
    
End Sub

'フォーカスが外れたとき
Private Sub txtKingaku1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku1
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With
    
End Sub

'フォーカスがあたったとき
Private Sub txtKingaku10_Enter()

    With Me.txtKingaku10
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku10_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku10
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku11_Enter()

    With Me.txtKingaku11
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku11_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku11
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku12_Enter()

    With Me.txtKingaku12
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku12_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku12
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku13_Enter()

    With Me.txtKingaku13
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku13_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku13
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku14_Enter()

    With Me.txtKingaku14
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku14_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku14
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku15_Enter()

    With Me.txtKingaku15
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku15_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku15
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With
    
End Sub

'フォーカスがあたったとき
Private Sub txtKingaku2_Enter()

    With Me.txtKingaku2
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku2
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku3_Enter()

    With Me.txtKingaku3
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku3
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku4_Enter()

    With Me.txtKingaku4
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku4
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku5_Enter()

    With Me.txtKingaku5
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku5
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku6_Enter()

    With Me.txtKingaku6
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku6_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku6
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku7_Enter()

    With Me.txtKingaku7
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku7_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku7
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォーカスがあたったとき
Private Sub txtKingaku8_Enter()

    With Me.txtKingaku8
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku8_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku8
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

Private Sub txtKingaku9_Change()

End Sub
'フォーカスがあたったとき
Private Sub txtKingaku9_Enter()

    With Me.txtKingaku9
        .SelStart = 0
        .SelLength = Len(.Text)
        .BackColor = RGB(255, 255, 0)
        .TextAlign = 1
        .IMEMode = 3
    End With

End Sub

'フォーカスが外れたとき
Private Sub txtKingaku9_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    With Me.txtKingaku9
        .BackColor = RGB(255, 255, 255)
        .TextAlign = 3
        .Text = Format$(.Text, "#,##0")
        .IMEMode = 0
    End With

End Sub

'フォームロード時
Private Sub UserForm_Initialize()

    '初期値設定
    Call NewData
    '社員リスト
    Call GetSyainList
    
End Sub

'フォームクローズ時
Private Sub UserForm_Terminate()

    Dim i As Integer
    
    '配列の開放
    If IsEmpty(mobjWS) = True Then Erase mobjWS
    'オブジェクトの開放
    For i = 0 To 2
        Set mobjWS(i) = Nothing
    Next
    
End Sub

'ワークシートの値の取得
Private Sub GetData()

    Dim clsTaikei As clsSalary      '給与体系クラス
    Dim objText As MSForms.TextBox  'テキストボックス
    Dim A() As Variant              'データ
    Dim lngRow As Long              'カレントレコード
    Dim i As Integer                '整数型カウンタ
    
    '給与体系クラスのインスタンス
    Set clsTaikei = New clsSalary
    '配列の再定義
    ReDim A(24) As Variant
    
    On Error GoTo ErrorProc
    'カレントレコードの取得
    mobjWS(0).Activate
    lngRow = mobjWS(0).Range(Cells(1, 2), Cells(mlngRow, 2)).Find(mlngFID, Cells(mlngRow, 2), xlValues).Row
    'カレントレコードの移動
    mlngRow = lngRow - 1
    'ワークシート値の取得
    clsTaikei.GetProperty mobjWS(0), mlngRow + 1, _
                        A(0), A(1), A(2), A(3), A(4), A(5), A(6), A(7), A(8), A(9), A(10), A(11), A(12), _
                        A(13), A(14), A(15), A(16), A(17), A(18), A(19), A(20), A(21), A(22), A(23), A(24)
    
    'ダミーデータは無視
    For i = 1 To 15
        Set objText = Controls("txtKingaku" & CStr(i))
        With objText
            .Text = Format$(A(i + 3), "#,##0")
            .TextAlign = 3
        End With
    Next
    
    'クラスの開放
    Set clsTaikei = Nothing
    'オブジェクトの開放
    Set objText = Nothing
    
    Exit Sub
    
ErrorProc:

    For i = 1 To 15
        Set objText = Controls("txtKingaku" & CStr(i))
        With objText
            .Text = 0
            .TextAlign = 3
        End With
    Next
    
    'カレントレコードの再取得
    If mobjWS(0).Cells(1, 1).Value = "" Then
        mlngRow = 0
    Else
        mlngRow = mobjWS(0).Cells(65536, 1).End(xlUp).Row
    End If
    'オブジェクトの開放
    Set objText = Nothing
    
End Sub
   
'ワークシートへ書き出し
Private Sub LetData()

    Dim clsTaikei As clsSalary      '給与体系クラス
    Dim objText As MSForms.TextBox  'テキストボックス
    Dim A() As Variant              'データ
    Dim i As Integer                '整数型カウンタ
    
    '配列再定義
    ReDim A(24) As Variant
    'テキストボックスデータを配列へ
    A(0) = mlngRow + 1
    A(1) = mlngFID
    A(2) = 0        'ダミー
    A(3) = Now()    'ダミー
    
    For i = 1 To 20
        If i < 16 Then
            Set objText = Controls("txtKingaku" & CStr(i))
            A(i + 3) = objText.Text
        Else
            A(i + 3) = 0  'ダミー
        End If
    Next
    
    '給与体系クラスのインスタンス
    Set clsTaikei = New clsSalary
    'ワークシートへ書き出し
    clsTaikei.LetProperty mobjWS(0), mlngRow + 1, _
                        A(0), A(1), A(2), A(3), A(4), A(5), A(6), A(7), A(8), A(9), A(10), A(11), A(12), _
                        A(13), A(14), A(15), A(16), A(17), A(18), A(19), A(20), A(21), A(22), A(23), A(24)
    
    MsgBox "正常に登録しました。", 64 + 0, "給与計算システム"
    
    '配列の開放
    If IsEmpty(A) = True Then Erase A
    'クラスの開放
    Set clsTaikei = Nothing
    'オブジェクトの開放
    Set objText = Nothing
    
End Sub
'社員リストの取得
Private Sub GetSyainList()
    
    Dim clsSyainID() As clsList     '社員リストクラス
    Dim A() As Variant              'データ
    Dim lngRow As Long              '社員データ数
    Dim i As Long                   '長整数型カウンタ
    Dim j As Long                   '配列要素数
    
    '社員リストの有無
    If mobjWS(1).Cells(1, 1).Value = "" Then GoTo BlankProc
    
    '配列要素数の取得
    lngRow = mobjWS(1).Cells(65536, 1).End(xlUp).Row
    '退職の検査
    j = 0
    For i = 1 To lngRow
        If mobjWS(1).Cells(i, 6).Value = 0 Then j = j + 1
    Next
    '要素がないとき
    If j < 1 Then Exit Sub
    
    ReDim clsSyainID(j - 1) As clsList  'クラス再定義
    ReDim A(j - 1, 1) As Variant        '配列再定義
    j = 0
    For i = 0 To lngRow - 1
        If mobjWS(1).Cells(i + 1, 6).Value = 0 Then
            '社員リストクラスのインスタンス
            Set clsSyainID(j) = New clsList
            '社員リストの取得
            clsSyainID(j).GetData mobjWS(1), i + 1, 1, 1, 4
            '配列に格納
            A(j, 0) = clsSyainID(j).id
            A(j, 1) = clsSyainID(j).Name
            j = j + 1
        End If
    Next
    
    With Me.lstSyain
        .Clear
        .ColumnCount = 2
        .List() = A
    End With
    
    If IsEmpty(A) = True Then Erase A
    
    'クラスの開放
    For i = 0 To j - 1
        Set clsSyainID(i) = Nothing
    Next
    
    Exit Sub
    
BlankProc:

    MsgBox "社員リストは空です。" & vbCrLf & "先に社員給与計算システムを登録して下さい。", 48 + 0, "給与計算システム"
        
End Sub

'初期値設定
Private Sub NewData()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim objLB As MSForms.Label      'ラベル
    Dim i As Integer                '整数型カウンタ
    
    ReDim mobjWS(2) As Worksheet    '配列再定義
    
    'ワークシート
    Set mobjWS(0) = Workbooks(gstrName).Worksheets("Sheet4")
    Set mobjWS(1) = Workbooks(gstrName).Worksheets("Sheet2")
    Set mobjWS(2) = Workbooks(gstrName).Worksheets("Sheet1")
    
    'レコードの取得
    If mobjWS(0).Cells(1, 1).Value = "" Then
        mlngRow = 0
    Else
        mlngRow = mobjWS(0).Cells(65536, 1).End(xlUp).Row
    End If
    
    For i = 1 To 15
        'ラベル
        Set objLB = Controls("lblKoumoku" & CStr(i))
        objLB.TextAlign = 3
        'テキストボックス
        Set objText = Controls("txtKingaku" & CStr(i))
        With objText
            If i > 6 And i < 12 And .Visible = True Then .Visible = False
            .Enabled = False
            .Text = ""
        End With
    Next
    
    'リスト
    With Me.lstSyain
        .ColumnWidths = "0;72"
        .ListIndex = -1
    End With
    
    Call VisibleTextBox
    'オブジェクトの開放
    Set objText = Nothing
    Set objLB = Nothing
    
End Sub

'テキストボックスの表示／非表示
Private Sub VisibleTextBox()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim objLB As MSForms.Label      'ラベル
    Dim i As Integer                '整数型カウンタ
    
    For i = 7 To 11
        'ラベル
        Set objLB = Controls("lblKoumoku" & CStr(i))
        If mobjWS(2).Cells(4, i - 6).Value <> "" Then objLB.Caption = mobjWS(2).Cells(4, i - 6).Value & " ："
        'テキストボックス
        If objLB.Caption <> "" Then
            Set objText = Controls("txtKingaku" & CStr(i))
            If objText.Visible = False Then objText.Visible = True
        End If
    Next
    
    On Error GoTo ErrorProc
    '政府管掌社会保険加入の有無
    If mobjWS(2).Cells(2, 1).Value = 0 Then
        For i = 13 To 15
            'ラベル
            Set objLB = Controls("lblKoumoku" & CStr(i))
            If objLB.Visible = True Then objLB.Visible = False
            'テキストボックス
            Set objText = Controls("txtKingaku" & CStr(i))
            If objText.Visible = True Then objText.Visible = False
        Next
    End If
    
    'オブジェクトの開放
    Set objText = Nothing
    Set objLB = Nothing
    
    Exit Sub
    
ErrorProc:

    MsgBox "設定項目が異常です。" & vbCrLf & "データファイルを作成し直して下さい。", 16 + 0, "給与計算システム"
    
End Sub