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

[mortezataheri]

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

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

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

[I Love VB6]

txtEmployeeNumber.Value

Textboxes do not have a .Value property

Try txtEmployeeNumber.Text

[I Love VB6]

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

[Elroy]

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:

    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

[SamOscarBrown]

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:

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

[dilettante]

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

[Shaggy Hiker]

Indeed. Thread moved to Office Development.

[mortezataheri]

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]

[westconn1]

I cant upload my file cuz the invalid extension

zip first

[mortezataheri]

zip first

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

[westconn1]

rar should be acceptable but most will not open

i tested in your file

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

[mortezataheri]

I tested in your file

this works for me

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

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

[mortezataheri]

This code works:

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