开发者

Pick data from excel sheet ( vba )

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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜