CODEWITHSUNDEEP
vbaexcel
mark
mark

Reputation: 13

How to Extract data from 2 sheets of excel and display them in VB form fields

I have data in 2 sheets excel say sheet2 and sheet3 and values in column1 of both sheets is same. So i created a userform with 1 dropdown(Combobox1), 4 textbox field and 1 listbox field.

dropdown picks data from column 1(ex. A2:20) of sheet2 and then once a value is selected it should display corresponding row values in textbox respectively for example: from sheet2 corresponding col-2 should be displayed in textbox1, col-3 in textbox2, col4 in textbox3, col5 in textbox4. while from sheet3 it should match the row where column1 value matched the selected value from from dropdown(combobox) and all corresponding values(in Col 2 - Col52) should be displayed in userform ListBox1. I am new to vba and excel so with help of google finally i was able to perform some of above mentioned tasks but i am not getting any head and tail how to connect selected range values to textbox'es and listbox. Any help will be highly appreciated and will be a good learning lesson for me. here's the half and not working code i created.

Private Sub ComboBox1_Change()

Application.ScreenUpdating = False


Dim CL As Object

Worksheets(2).Select

For Each CL In Worksheets(2).Range("A2:A20")
If CL = ComboBox1.Text Then
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy Destination:=ActiveCell.Offset(0, 5)

End If
Next
Worksheets(2).Select
End Sub



Private Sub UserForm_Activate()
ComboBox1.RowSource = "A2:A20"
End Sub

Upvotes: 1

Views: 5638

Answers (1)

Alex P
Alex P

Reputation: 12497

This may get you on the right track:

Private Sub ComboBox1_Change()
    Dim rw As Long, cl As Range

    rw = WorksheetFunction.Match(Me.ComboBox1.Value, Worksheets("Sheet2").Range("A1:A20"), 0)
    TextBox1 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 1)
    TextBox2 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 2)
    TextBox3 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 3)
    TextBox4 = Worksheets("Sheet2").Range("A" & rw).Offset(0, 4)

    ListBox1.Clear

    For Each cl In Worksheets("Sheet3").Range("B" & rw & ":AZ" & rw) //Here AZ is column 52 in worksheet
        ListBox1.AddItem cl
    Next cl
End Sub

Private Sub UserForm_Activate()
    ComboBox1.RowSource = "A1:A20"
End Sub

Update

If you want the ListBox to show data horizontally then:

  1. In ListBox properties set ColumnHeads to True
  2. In ListBox properties set ColumnCount equel to 52

Now replace For each cl... code aboe with:

Dim rng As Range
Set rng = Worksheets("Sheet3").Range("B" & rw & ":AZ" & rw)
Me.ListBox1.RowSource = rng.Address

Upvotes: 1

Related Questions