Pick data from excel sheet ( vba )

Question

i have a excel sheet name_data, and sheet contain 3 column (A1=ID,B1=NAME AND C1=MARK) What my doubt is ,in vba userform i enter or select the STUDENTS ID in Combobox1 and press A COMMAND button , it should show the Students NAME in Textbox1 and Students Mark in Text2
data stored in same excel sheet,and went vba in alt+F11

Kindly advice how to solve this problem

Can you show your existing code within the userform that is trying to retrieve Name and Mark? — Alex P

Alex P Alex P · Accepted Answer · 2010-12-26T14:31:39

Let's assume you have data in cells A1 to C10 with the first row holding the column names:

    A       B           C
1   ID      Name        Mark
2   1       Raj         50
3   2       Sulieman    45
4   etc...

On your Userform you have the following:

ComboBox1 - contains list of IDs
CommandButton1 - click this to get student information
TextBox1 - shows name of student based on ID selected in combobox1
TextBox2 - shows mark of student based on ID selected in combobox1

In your Userform, add the following code:

Private Sub UserForm_Initialize()
    Dim comboBoxItems As Range, Dim cl As Range

    Set comboBoxItems = Worksheets(1).Range("A2:A10")

    For Each cl In comboBoxItems //Populate combobox when userform launched
        Me.ComboBox1.AddItem (cl.Value)
    Next cl
End Sub

Private Sub CommandButton1_Click()
    Dim ID As Long
    Dim Name As String, Dim Mark As String, 
    Dim tableRng As Range

    Set tableRng = Worksheets(1).Range("A1:C10")

    ID = Me.ComboBox1.Value //Get ID selected in combobox
    Name = Application.WorksheetFunction.VLookup(ID, tableRng, 2, False)
    Mark = Application.WorksheetFunction.VLookup(ID, tableRng, 3, False)

    Me.TextBox1 = Name 
    Me.TextBox2 = Mark 
End Sub

Pick data from excel sheet ( vba )

1 Answers