CODEWITHSUNDEEP
vbaexcel
Txon
Txon

Reputation: 1

How to get datas from multiple sheet to userform textboxes

I have a userform where i have to get datas from few sheets to the textboxes in userform. My coding is only tranfering datas from the active sheet.

I have a multipage in my userform where page1(part info) with 7 textboxes and page 2(stock info)with 5 textboxes. All this textboxes are to populate information from excel sheets.

Once I key in the partno in textbox1, all the textboxes in multipage must auto populate. I've got no issues with getting data for page 1 cause all the datas in page1 comes from sheet 1(part details).

But in page 2 i need data's from:

Please check my codes and advice me what are the changes i must do.

Thanks.

Below are my codes and userform pics.

Sub GetData()
    '--------------------------------------
    ' Check database for entry in Textbox1 _
     and if in DB then populate other TB _
     and image
    '--------------------------------------

    Dim j As Integer
    Dim rFound As Range
    Dim wsData1 As Worksheet
    Dim shImage As Shape
    Dim sID As String
    Dim wsData2 As Worksheet

    Set wsData1 = ActiveWorkbook.Sheets("Part Details")     
    With wsData1
       ' Get the item number in sID
        sID = Me.TextBoxs1.Value
        ' check column A of the datasheet for the entry
        Set rFound = Columns("A").Find(what:=sID, _
                                    after:=.Cells(1, 1))
        ' if found, process. else quit
        If Not rFound Is Nothing Then ' This checks that rFound is set to an object and not 'nothing'
            ' Load the details in the text boxes
            For j = 2 To 11
                Me.Controls("TextBoxs" & j).Value = rFound.Offset(0, j -1).Value
            Next j

            ' load the image into the image holder
            ' rFound.Row is the row where we need to look for the data
             Set shImage = GetImage(rFound.Row)
             If Not shImage Is Nothing Then
                 ' valid image found
                 shImage.Copy
                 Set Image1.Picture = PastePicture(xlPicture)
             End If
         Else
            ClearForm bAll:=False ' keep text in textbox1
         End If
     End With

    '----------------------------------------------------------------
    ' IM STUCKED FROM HERE
    '----------------------------------------------------------------       

    Set wsData2 = ActiveWorkbook.Sheets("Stock Update")
    With wsData2
        ' Get the item number in sID
        sID = Me.TextBoxs1.Value

        ' check column A of the datasheet for the entry
        Set rFound = Columns("A").Find(what:=sID, _
                                    after:=.Cells(1, 1))
        ' if found, process. else quit
        If Not rFound Is Nothing Then ' This checks that rFound is set to an 
                                        object and not 'nothing'

            ' Load the details in the text boxes
            TextBoxs12.Value = rFound.Offset(0, 3).Value
        End If
    End With
End Sub

Page1

Page2

Upvotes: 0

Views: 651

Answers (1)

Variatus
Variatus

Reputation: 14383

Your code 

Set rFound = Columns("A").Find(what:=sID, after:=.Cells(1, 1))

refers to the active sheet's column A. If you want it to refer to WsData1's column A you must precede the column reference with a period, like .Columns("A").Find. With that done, you can use the same setup you have on any datasheet you specify.

Upvotes: 1

Related Questions