combobox uchun txtbox qiymatini avtomatik ravishda o'zgartirish

Menda "A" ustunida ishchi ismlar va 1-sonda "B" ustunidagi xodimlar soni bor. Foydalanuvchi shaklida menda ishlaydigan nomlarni ko'rsatadigan combobox bor, men uning ishchilariga comboboxda ism tanlanganida kerakligini bilaman. Yaqinda txtboxda ko'rsatilgan raqam va qanday qilib men bilmayman.

Me.cboNames
Me.txtEmployeeNumber
0

6 javoblar

Quyidagi kod Worksheets ("Sheet4") dan (barcha varaqlarning ismini o'zgartiring) ComboBox (loopsiz) uchun barcha qiymatlarni yuklaydi. Keyinchalik, Change hodisasi oynasida matn qutisidagi qiymatni o'zgartiradi.

Note: If you have a header row, and you data starts from the 2nd row, modify the line below :

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

quyidagilar:

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

Code (inside the User_Form module):

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
0
qo'shib qo'ydi
@Mansour qaysi kodning kodini buzadi?
qo'shib qo'ydi muallif Shai Rado, manba
@Mansour, User_form-da joylashgan cboNames nomli combo-box-dir?
qo'shib qo'ydi muallif Shai Rado, manba
siz bu kodni eslatgan 2 user_form tekshiruvi bilan ishlamaysiz, nusxa ko'chirishning usulini topishingiz kerak >> ularni kodingizda joylashtirish
qo'shib qo'ydi muallif Shai Rado, manba
Salom Shay, kodni UF modulida (har ikkisi) qo'ydim, lekin "Argumentlar aniqlanmagan" txtEmployeeNumber.ValueWorksheets ("Sheet4") kompilyatsiya xatosi beradi .Haytalar (** & zwnj; cboNames **. ListIndex + 1, 2)
qo'shib qo'ydi muallif Mansour, manba
Excel 2007 dan foydalanishni bilaman va ListIndex haqida hech qanday ma'lumot yo'q va buni carbonbon xususiyatlari oynasida ko'rmayapman
qo'shib qo'ydi muallif Mansour, manba
Kechirasiz, men 2 ta boshqa nazorat haqida gapirmasligim kerak, kodni hozircha o'qimaganman. mening muammom hali ham mavjud.
qo'shib qo'ydi muallif Mansour, manba

Quyidagi kod Worksheets ("Sheet4") dan (barcha varaqlarning ismini o'zgartiring) ComboBox (loopsiz) uchun barcha qiymatlarni yuklaydi. Keyinchalik, Change hodisasi oynasida matn qutisidagi qiymatni o'zgartiradi.

Note: If you have a header row, and you data starts from the 2nd row, modify the line below :

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

quyidagilar:

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

Code (inside the User_Form module):

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
0
qo'shib qo'ydi
@Mansour qaysi kodning kodini buzadi?
qo'shib qo'ydi muallif Shai Rado, manba
@Mansour, User_form-da joylashgan cboNames nomli combo-box-dir?
qo'shib qo'ydi muallif Shai Rado, manba
siz bu kodni eslatgan 2 user_form tekshiruvi bilan ishlamaysiz, nusxa ko'chirishning usulini topishingiz kerak >> ularni kodingizda joylashtirish
qo'shib qo'ydi muallif Shai Rado, manba
Salom Shay, kodni UF modulida (har ikkisi) qo'ydim, lekin "Argumentlar aniqlanmagan" txtEmployeeNumber.ValueWorksheets ("Sheet4") kompilyatsiya xatosi beradi .Haytalar (** & zwnj; cboNames **. ListIndex + 1, 2)
qo'shib qo'ydi muallif Mansour, manba
Excel 2007 dan foydalanishni bilaman va ListIndex haqida hech qanday ma'lumot yo'q va buni carbonbon xususiyatlari oynasida ko'rmayapman
qo'shib qo'ydi muallif Mansour, manba
Kechirasiz, men 2 ta boshqa nazorat haqida gapirmasligim kerak, kodni hozircha o'qimaganman. mening muammom hali ham mavjud.
qo'shib qo'ydi muallif Mansour, manba

Bunga o'xshash biror narsa bo'lishi mumkin (ma'lumotlarni "Xodim" varaqida deb hisoblasangiz):

Private Sub UserForm_Initialize()

lLastRowEmployee = Worksheets("Employee").Cells(1, 1).End(xlDown).Row 'find las row with data

    For iC = 1 To lLastRowEmployee
        ComboBox1.AddItem Sheets("Employee").Cells(iC, 1) 'load combobox
    Next
End Sub

Private Sub ComboBox1_Change()
    TextBox1 = Worksheets("Employee").Cells(ComboBox1.ListIndex + 1, 2) 'if combo changes, show employee number in texbox1
End Sub

Agar siz raqamni ko'rsatishni xohlasangiz, matn qutisi o'rniga yorliqni ishlatishni o'ylab ko'ring.

0
qo'shib qo'ydi

Bunga o'xshash biror narsa bo'lishi mumkin (ma'lumotlarni "Xodim" varaqida deb hisoblasangiz):

Private Sub UserForm_Initialize()

lLastRowEmployee = Worksheets("Employee").Cells(1, 1).End(xlDown).Row 'find las row with data

    For iC = 1 To lLastRowEmployee
        ComboBox1.AddItem Sheets("Employee").Cells(iC, 1) 'load combobox
    Next
End Sub

Private Sub ComboBox1_Change()
    TextBox1 = Worksheets("Employee").Cells(ComboBox1.ListIndex + 1, 2) 'if combo changes, show employee number in texbox1
End Sub

Agar siz raqamni ko'rsatishni xohlasangiz, matn qutisi o'rniga yorliqni ishlatishni o'ylab ko'ring.

0
qo'shib qo'ydi

Ushbu kod ishlaydi

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 combobox data worksheet and range 
            txtEmployeeNumber.Text = .Index(Sheets("sheet1").Range("B2:B100"), Row) '< your textbox data worksheet and range

        End With
    End If
End Sub
0
qo'shib qo'ydi

Ushbu kod ishlaydi

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 combobox data worksheet and range 
            txtEmployeeNumber.Text = .Index(Sheets("sheet1").Range("B2:B100"), Row) '< your textbox data worksheet and range

        End With
    End If
End Sub
0
qo'shib qo'ydi