PDA

View Full Version : VBA userform combobox and vlookup to fill another cells



Sharpeye
09-07-2017, 09:11 AM
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
10-02-2017, 01:25 PM
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.