[RESOLVED] Automatically change textbox data accourding to combobox selected Item

I have some employee Names on column "A" and relevant employee Numbers on column "B" in sheet1. On userform I have a combobox that shows employee names,i want when a name is selected on combobox his/her employee Number shown on a nearby txtbox.
I know a code

Code:

---

Option Explicit

Private Sub cboNames_Change()

txtEmployeeNumber.Value = Worksheets("Sheet4").Cells(cboNames.ListIndex + 1, 2)

End Sub

'=========================================================================

Private Sub UserForm_Initialize()

Dim LastRow As Long

cboNames.Clear
With Worksheets("Sheet4") '<--replace "Sheet4" with the sheet you have your employees data
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    cboNames.List = Application.Transpose(.Range("A2:A" & LastRow).Value)
End With

End Sub

---

I've put it in Userform module But it gives Compile error "Variable not defined" on this line

Code:

---

txtEmployeeNumber.Value = Worksheets("Sheet4").Cells(cboNames.ListIndex + 1, 2)

---

txtEmployeeNumber.Value

Textboxes do not have a .Value property

Try txtEmployeeNumber.Text

Private Sub cboNames_Change()

txtEmployeeNumber.Value = Worksheets("Sheet4").Cells(cboNames.ListIndex + 1, 2)

End Sub



You have a sub called cboNames_Change so that tells me you have a combobox called cboNames so I don't believe that you can have a compile error "Variable not defined" as you say for this control

Just as a tip, when a moderately sophisticated line like the one you posted at the bottom of your #1 post gives you an error, and you can't get it sorted, break it into several lines.

The following is rather extreme, but it gives you the idea:

Code:

---


    Dim rng As Range
    Dim idx As Long
    Dim sVal As String

    idx = cboNames.ListIndex + 1
    If idx <> -1 Then
        Set rng = Worksheets("Sheet4").Cells(idx + 1, 2) ' <---- Hopefully, Worksheets has been set.
        sVal = rng.Value
        txtEmployeeNumber.Value = sVal ' <---- This line will fail because TextBox has no Value property.
    End If



---

Regards,
Elroy

I don't know what you're doing wrong, mort, but here is a quick example using basically what you are doing, and it works just fine:

Code:

---

Option Explicit
Dim AppExcel As Excel.Application
Dim objWb As Excel.Workbook

Private Sub cboNames_Click()  'NOTICE-I used the CLICK EVENT, not the CHANGE EVENT IN THE ComboBox, cboNames
    txtEmployeeNumber.Text = objWb.Worksheets(1).Cells(cboNames.ListIndex + 1, 2) 'NOTICE THE .Text, not .Value (as stated by others above)  '''also NOTE: I only have one worksheet in my workbook, hence (1).
End Sub



Private Sub Form_Load()
    cboNames.AddItem ("John")
    cboNames.AddItem ("Sam")
    cboNames.AddItem ("Sammi")
    cboNames.AddItem ("Bill")
    cboNames.AddItem ("James")
    Set AppExcel = CreateObject("Excel.Application")
    Set objWb = AppExcel.Workbooks.Add
    Set objWb = GetObject(App.Path & "\testfile.xlsx") 
End Sub

---

My Excel file (textfile.xlsx) looks like this:
Column A Column B
John 1
Sam 2
Sammie 3
Bill 4
James 5

This isn't a VB question. The posted snippets make it clear this is Office VBA and the thread is in the wrong forum.

Indeed. Thread moved to Office Development.

Code:

---

Option Explicit
Dim AppExcel As Excel.Application
Dim objWb As Excel.Workbook

Private Sub cboNames_Click()  'NOTICE-I used the CLICK EVENT, not the CHANGE EVENT IN THE ComboBox, cboNames
    txtEmployeeNumber.Text = objWb.Worksheets(1).Cells(cboNames.ListIndex + 1, 2) 'NOTICE THE .Text, not .Value (as stated by others above)  '''also NOTE: I only have one worksheet in my workbook, hence (1).
End Sub

Private Sub Form_Load()
    cboNames.AddItem ("John")
    cboNames.AddItem ("Sam")
    cboNames.AddItem ("Sammi")
    cboNames.AddItem ("Bill")
    cboNames.AddItem ("James")
    Set AppExcel = CreateObject("Excel.Application")
    Set objWb = AppExcel.Workbooks.Add
    Set objWb = GetObject(App.Path & "\testfile.xlsx") 
End Sub

---

Regardless changing parameter to suit my project again it gives the same error at the first step when i debug
I think there's a problem with my workbook, Of course it is not perfect yet,I'm trying to complete my project step by step Although when i apply for debug everything seems ok. I try to attach the file here please spend 5 -10 min to check it
Thanks

Edit: I cant upload my file cuz the invalid extension. Is it possible to mail someone who like to help?If yes please inform me
[email protected]

Quote:

---

I cant upload my file cuz the invalid extension

---

zip first

Quote:

---

zip first

---

I changed to zip by win zip but still is unacceptable,cuz it chanhed to rar not to zip

rar should be acceptable but most will not open

i tested in your file

Code:

---

Private Sub cboName_Click()
txtEmployeeNumber.Value = Worksheets("Sheet1").Cells(cboName.ListIndex + 2, 2)
End Sub

---

this works for me, you have the name for your combobox incorrect, in the OP

Quote:

---

I tested in your file

---

Quote:

---

this works for me

---

you mean that code worked on your own file or after fixing my file tested in mine then it worked?

Quote:

---

you have the name for your combobox incorrect, in the OP

---

What's the meaning of OP ? irf you mean Original post, i changed parameters to suit my file then tested

This code works:

Code:

---

Private Sub cboName_Change() '< your combobox
    Dim EName As String
    Dim Row As Integer
    EName = Me.cboName.Text
    If EName <> "" Then
        With Application.WorksheetFunction
            Row = .Match(EName, Sheets("sheet1").Range("A2:A100"), 0) '< your data worksheet and range
            txtEmployeeNumber.Text = .Index(Sheets("sheet1").Range("B2:B100"), Row) '< your textbox
            txtShift.Text = .Index(Sheets("sheet1").Range("C2:C100"), Row)
        End With
    End If
End Sub

---

I will tick as solved
Thanks to all