Combo to apply different value based on person selected

[AGrace]

Hi all,

I have a holiday planner that has a userform which can be used to determine the number of days holiday each individual has taken off. However, recently there have been some changes whereby certain individuals have received a larger holiday allowance. So my current code is set to cater for just 20 days a year across the board.

see below.

Private Sub ComboBox1_Change()
    Dim lngRow As Long
    Dim rngData As Range
    If Me.ComboBox1.ListIndex <> -1 Then
        ' get row number - employee range starts in row 6
        lngRow = 6 + Me.ComboBox1.ListIndex
        With Sheet1
            Set rngData = .Range(.Cells(lngRow, "C"), .Cells(lngRow, "NC"))
        End With
        Me.TextBox1.Value = CountByColor(rngData, Sheet1.Range("B23")) ' holiday
        Me.TextBox2.Value = CountByColor(rngData, Sheet1.Range("B24")) ' sick leave
        Me.TextBox3.Value = CountByColor(rngData, Sheet1.Range("B25")) ' other
    End If
    
    TextBox4.Value = TextBox5.Value - TextBox1.Value
    
End Sub

I would like to apply a filter or something similar that looks at the name in the combobox and determines whether the employee gets 20 or 22 days a year.

Any thought's on how I can go about this?

As always, your time and help is greatly appreciated!

Thanks,

Adam

 

[AlphaFrog]

I would suggest that when you populate ComboBox1 with the employee names, you can also populate it with their holiday allowance in the combobox 2nd column. You don't have to display the combobox 2nd column.

Then when you select a name from the combox, you can read the value from the 2nd column into TextBox5.

If you post the code that populates the combobox and specify what column has their Holiday allowance for each name, then I can show you how to populate the 2nd column with the holiday allowance.