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
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