シート上にコンボボツクスを３つ設定する

絞り込み検索する

 

Private Sub Workbook_Open()

 

Dim ITE As Variant

Dim flg As Variant

Dim ico As Long

Dim i As Long

 

  ico = 1

  With ThisWorkbook.Worksheets("マスタ")

    Do While .Cells(ico, 1) <> ""

      ITE = .Cells(ico, 1).Value

      flg = 0

      For i = 0 To Sheets("個人").ComboBox1.ListCount - 1

        If ITE = Sheets("個人").ComboBox1.List(i) Then flg = 1

      Next i

      If flg = 0 Then Sheets("個人").ComboBox1.AddItem ITE

      ico = ico + 1

    Loop

  End With

 

  Dim ico2 As Long

  ico2 = 1

  With ThisWorkbook.Worksheets("マスタ")

    Do While .Cells(ico2, 3) <> ""

      ITE = .Cells(ico2, 3).Value

      flg = 0

      For i = 0 To Sheets("個人").ComboBox3.ListCount - 1

        If ITE = Sheets("個人").ComboBox3.List(i) Then flg = 1

      Next i

      If flg = 0 Then Sheets("個人").ComboBox3.AddItem ITE

      ico2 = ico2 + 1

    Loop

  End With

'  Me.ComboBox1.SetFocus

 

End Sub

 

モジュール

Sub 品名分類() '品名分類セット'★

Dim ITE As Variant

Dim flg As Variant

Dim ico As Long

Dim i As Long

   ico = 2

        With Worksheets("マスタ")

                Do While .Cells(ico, 1) <> ""

                  ITE = .Cells(ico, 1).Value

                  flg = 0

                  For i = 0 To Sheets("個人").ComboBox1.ListCount - 1

                    If ITE = Sheets("個人").ComboBox1.List(i) Then flg = 1

                  Next i

                  If flg = 0 Then Sheets("個人").ComboBox1.AddItem ITE

                  ico = ico + 1

                Loop

        End With

 End Sub

Sub 品名セット() '★

Dim ITE As Variant

 Dim flg As Variant

 Dim key As String       '文字

 Dim i As Integer

 Dim ico As Long

                 'ComboBox5セット

   ico = 1 '読み込みＹ座標

  With ThisWorkbook.Worksheets("マスタ")

     key = Sheets("個人").ComboBox1.Text '１つ前の値をキーにする

     Sheets("個人").ComboBox2.Clear 'コンボボックスクリア

             Do While .Cells(ico, 1) <> "" 'リストの最後までループ

                If .Cells(ico, 2) = key Then 'Ａ列がキーの値だったら

                    ITE = .Cells(ico, 3).Value 'Ｂ列の値をＩＴＥに

                    flg = 0 '追加フラグ

                    For i = 0 To Sheets("個人").ComboBox2.ListCount - 1 'コンボボックス２をループ

                   

                         If ITE = Sheets("個人").ComboBox2.List(i) Then flg = 1 'ＩＴＥの値がすでにコンボボックス２に入っている

                     Next

                     If flg = 0 Then Sheets("個人").ComboBox2.AddItem ITE 'ＦＬＧが０だったらＩＴＥをコンボボックスに追加

                 End If

                 ico = ico + 1 '読み込みＹ座標＋１

            Loop

   End With

 

   End Sub

 

Option Explicit

 

Private Sub ComboBox1_Change() '事業場

Dim ITE As Variant

 Dim flg As Variant

 Dim key As String       '文字

 Dim i As Integer

 Dim ico As Long

                 'ComboBox5セット

   ico = 1 '読み込みＹ座標

  With ThisWorkbook.Worksheets("マスタ")

     key = Sheets("個人").ComboBox1.Text '１つ前の値をキーにする

        Sheets("個人").ComboBox2.Clear 'コンボボックスクリア

             Do While .Cells(ico, 1) <> "" 'リストの最後までループ

                If .Cells(ico, 1) = key Then 'Ａ列がキーの値だったら

                    ITE = .Cells(ico, 2).Value 'Ｂ列の値をＩＴＥに

                    flg = 0 '追加フラグ

                    For i = 0 To Sheets("個人").ComboBox2.ListCount - 1 'コンボボックス２をループ

                   

                         If ITE = Sheets("個人").ComboBox2.List(i) Then flg = 1 'ＩＴＥの値がすでにコンボボックス２に入っている

                     Next

                     If flg = 0 Then Sheets("個人").ComboBox2.AddItem ITE 'ＦＬＧが０だったらＩＴＥをコンボボックスに追加

                 End If

                 ico = ico + 1 '読み込みＹ座標＋１

            Loop

   End With

'    Sheets("個人").ComboBox2.SetFocus

'品名セット

End Sub

 

Private Sub ComboBox2_Change() '部署

 

Dim ITE As Variant

 Dim flg As Variant

 Dim key As String       '文字

 Dim key1 As String

 Dim i As Integer

 Dim ico As Long

                 'ComboBox5セット

'ComboBox3セット

 

  ico = 1

  With ThisWorkbook.Worksheets("マスタ")

    key = Sheets("個人").ComboBox1.Text

    key1 = Sheets("個人").ComboBox2.Text

  

 

    Sheets("個人").ComboBox3.Clear

   

    Do While .Cells(ico, 1) <> ""

        If .Cells(ico, 1) = key And .Cells(ico, 2) = key1 Then

        

            ITE = .Cells(ico, 3).Value

            flg = 0

            For i = 0 To Sheets("個人").ComboBox3.ListCount - 1

                If ITE = Sheets("個人").ComboBox3.List(i) Then flg = 1

            Next

            If flg = 0 Then Sheets("個人").ComboBox3.AddItem ITE

        End If

        ico = ico + 1

    Loop

  End With

'  Me.ComboBox3.SetFocus

  

''ComboBox3セット

'  Dim ico As Long

'  ico = 1

'  With ThisWorkbook.Worksheets("個人")

'    key = Me.ComboBox1.Text

'    key1 = Me.ComboBox2.Text

'

'

'    Me.ComboBox3.Clear

'

'    Do While .Cells(ico, 1) <> ""

'        If .Cells(ico, 1) = key And .Cells(ico, 2) = key1 Then

'

'            ITE = .Cells(ico, 3).Value

'            flg = 0

'            For i = 0 To Me.ComboBox3.ListCount - 1

'                If ITE = Me.ComboBox3.List(i) Then flg = 1

'            Next

'            If flg = 0 Then Me.ComboBox3.AddItem ITE

'        End If

'        ico = ico + 1

'    Loop

'  End With

'  Me.ComboBox3.SetFocus

End Sub

 

Private Sub ComboBox3_Change() '氏名

 

Worksheets("個人").Range("C2") = ComboBox3.Value

 'ComboBox4セット

'  Dim ico As Long

'  ico = 1

'  With ThisWorkbook.Worksheets("data")

'    key = Me.ComboBox1.Text

'    key1 = Me.ComboBox2.Text

'   key2 = Me.ComboBox3.Text

'key3 = CInt(combobox3text)

'

'    Me.ComboBox4.Clear

'

'    Do While .Cells(ico, 1) <> ""

'        If .Cells(ico, 1) = key And .Cells(ico, 2) = key1 And .Cells(ico, 3) = key2 Then

'

'            ITE = .Cells(ico, 4).Value

'            flg = 0

'            For i = 0 To Me.ComboBox4.ListCount - 1

'                If ITE = Me.ComboBox4.List(i) Then flg = 1

'            Next

'            If flg = 0 Then Me.ComboBox4.AddItem ITE

'        End If

'        ico = ico + 1

'    Loop

'  End With

'  Me.ComboBox4.SetFocus

End Sub