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+F11Kindly 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
精彩评论