Listbox not showing Data

[Maxicus]

Hi

I am having a problem with one of my codes not working at 100%. and need some help figuring out why.

I have a userform, that if i click on a button, it runs a sub function to call data from a specific place in the worksheet. (Sheet7)
the code works perfect if i have that sheet open, but it the worksheet is perhaps on a different sheet like Sheet6, the data is not transferred.

I will Show the two codes that i am using.
Firstly a button that triggers the sub function

Private Sub cmdSearch_Click()
Sheet7.Range("H2") = Me.Reg1.Value
    
        finddata

'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String

'error block
On Error GoTo errHandler:

'find the row with the data
cRow = Me.Reg1.Value
Set FindRow = Sheet2.Range("G:G").Find(What:=cRow, LookIn:=xlValues)

'add the values to the userform
Me.Reg1.Value = FindRow
Me.Reg2.Value = FindRow.Offset(0, -1)
Me.Reg3.Value = FindRow.Offset(0, 16)
Me.Reg4.Value = FindRow.Offset(0, 17)
Me.PPE1.Value = FindRow.Offset(0, 22)
Me.PPE2.Value = FindRow.Offset(0, 23)
Me.PPE3.Value = FindRow.Offset(0, 24)
Me.PPE4.Value = FindRow.Offset(0, 25)

'error block
On Error GoTo 0
Exit SuberrHandler:MsgBox "Error! Payroll ID Does Not Exist."

End Sub 

and the sub function (Finddata)

Sub finddata()
Dim ID As String
Dim finalrow As IntegerDim 
i As Integer

Sheet7.Range("J2:N101").ClearContents

ID = Sheet7.Range("H2").Text
finalrow = Sheet7.Range("A90000").End(xlUp).Row

For i = 2 To finalrow    
If Cells(i, 1) = ID Then       
       Range(Cells(i, 2), Cells(i, 5)).Copy       
       Range("J100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats        
       End If
Next i

lstSearch1.RowSource = Sheet7.Range("PPE").Address(external:=True)

End Sub 

I am only doing VBA coding for 2 weeks, so my knowledge on it is very limited.

[Maxicus]

Hi

I managed to solve the problem by inserting "Sheet7.Activate" in the Sub Function.

[snb]

You'd better use:

Private Sub cmdSearch_Click()
  sn = sheet7.UsedRange

  For j = 1 To UBound(sn)
    If sn(j, 7) = Reg1.Value Then Exit For
  Next

  If j <= UBound(sn) Then
    For jj = 1 To 4
      Me("Reg" & jj) = sn(j, Choose(j, 8, 7, 24, 25))
      Me("PPE" & j) = sn(j, Choose(j, 29, 30, 31, 32))
   next
 End If
End Sub