PDA

View Full Version : Listbox not showing Data



Maxicus
02-19-2018, 12:06 AM
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
02-19-2018, 12:31 AM
Hi

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

snb
02-19-2018, 02:52 AM
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