VBA userform combobox and vlookup to fill another cells

[Sharpeye]

Hello everyone,
I want to code a vacation request based on the combobox selected value
So atm i have a half functional combobox "cmbAngajat" unique list based on a range (the bad thing is that still shows "0" and "--" from the selected range) and the working period selection in cells with calendar.
I need the right code after i select the Employee in combobox to fill in worksheet "userform" these: cell B2 for employee position, cell C2 for employee ID.
Employees positions can be found in next sheet "CerereCO" range B5:B117, while employee ID is in another sheet "ActiveEmployee" range C2:C27.
So i need an vlookup code to fill these cells based on combobox selection.

Thank you in advance!

Option Explicit


Private Sub cmbAngajat_Click()


    var1 = WorksheetFunction.VLookup(cmbAngajat.Value, Worksheets("CerereCO").Range(Functia), 2, False)
    tbxFunctia.Value = var1


End Sub


Private Sub UserForm_Activate()


    Dim Cell        As Range
    Dim col         As Variant
    Dim Descending  As Boolean
    Dim Entries     As Collection
    Dim Items       As Variant
    Dim index       As Long
    Dim j           As Long
    Dim RngBeg      As Range
    Dim RngEnd      As Range
    Dim row         As Long
    Dim Sorted      As Boolean
    Dim temp        As Variant
    Dim test        As Variant
    Dim Wks         As Worksheet
    
        Set Wks = ThisWorkbook.Worksheets("CerereCO")
        
        Set RngBeg = Wks.Range("A5")
        col = RngBeg.Column
        
        Set RngEnd = Wks.Cells(Rows.count, col).End(xlUp)
        
            Set Entries = New Collection
            ReDim Items(0)
            
            For row = RngBeg.row To RngEnd.row
                Set Cell = Wks.Cells(row, col)
                    On Error Resume Next
                        test = Entries(Cell.Text)
                        If Err = 5 Then
                            Entries.Add index, Cell.Text
                            Items(index) = Cell.Text
                            index = index + 1
                            ReDim Preserve Items(index)
                        End If
                    On Error GoTo 0
            Next row
                        
        index = index - 1
        Descending = False  ' Set this to True to sort in descending order Z-A.
        ReDim Preserve Items(index)
        
            Do
                Sorted = True
                
                For j = 0 To index - 1
                    If Descending Xor StrComp(Items(j), Items(j + 1), vbTextCompare) = 1 Then
                        temp = Items(j + 1)
                        Items(j + 1) = Items(j)
                        Items(j) = temp
                        
                        Sorted = False
                    End If
                Next j
                
                index = index - 1
            Loop Until Sorted Or index < 1
                   
        cmbAngajat.List = Items
        
        Sheets("userform").Range("A2").Value = cmbAngajat.Value
End Sub


Private Sub DTPicker1_Change()
    Dim wsSheetRS As Worksheet
    Set wsSheetRS = Worksheets("userform")
    wsSheetRS.Range("A11") = DTPicker1.Value
End Sub


Private Sub DTPicker2_Change()
    Dim wsSheetRS As Worksheet
    Set wsSheetRS = Worksheets("userform")
    wsSheetRS.Range("B11") = DTPicker2.Value
End Sub


Sub cmbAngajat_Change()
    Sheets("userform").Range("A2").Value = cmbAngajat.Value
End Sub

[werafa]

Not sure if this will still be relevant, but:

is your info organised in a flat database structure?
if so, use your combobox selection to get a row number - and populate your form from this info.

you can use an event to trigger your code, and can read the value from the combobox as your way to get started.