VBA – Transfer filtered data from worksheet to Userform Textboxes

[wcm69]

Hi all really hoping for some help.

I would like to transfer a row of filtered data from my worksheet (Sheet1) into corresponding textboxes on a Userform. I would like to do this automatically by means of the Listbox- doubleclick event.

Currently: –
I have a listbox (named: lstLookup) which is populated by a selection made in a combobox dropdown list. The selected item (full name) from the listbox is then sent to the worksheet (Sheet1) where an advanced filter matches the name (criteria) and copies over the corresponding full details.

I would like on the (listbox) ‘double-click’ event for the filtered data to be automatically transferred back to the corresponding Userform (“Reg”) textboxes. I have tried a variety of codes found on the internet (including a VLookup) and none succeeded in transferring the (full details) data from the worksheet into the textboxes .

As usual any help is alway greatly appreciated as I’ve been trying to resolve this for days
The code for the filtered data is in a module named: sub Adv (). This filters the data and copies to Sheet1. Range V7:AD7 (i.e. 6 columns).

I have 9 textboxes named: Reg1 to Reg9 which need to receive this data.
The code I’m trying runs an error number: 91 “Object variable or With block variable not set”

Can't figure it out

Code tried:

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim fullName As String
Dim I As Integer
Dim findvalue
Dim cNum As Integer

On Error GoTo errHandler:

Me.lstLookup.RowSource = "Product"

'send selected item to criteria block to be filtered
Sheet1.Range("S7").Value = lstLookup.Value

'run advanced filter
Adv

'get the selected value from the listbox
For I = 0 To Me.lstLookup.ListCount - 1
If Me.lstLookup.Selected(I) = True Then
fullName = lstLookup.List(I, 0)
End If

Next I

'find the name
Set findvalue = Sheet1.Range("G:G").Find(What:=fullName, LookIn:=xlValues).Offset(0, -3)

'add the database values to the userform
cNum = 9
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next

errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"

End Sub

Many thanks in advance

 

[MARK858]

What line does it error on if you comment out the error handler? and what code is missing where I have changed the font to red in the code you posted below?

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 'declare the variables
 Dim fullName As String
 Dim I As Integer
 Dim findvalue
 Dim cNum As Integer

 On Error GoTo errHandler:

 Me.lstLookup.RowSource = "Product"

 'send selected item to criteria block to be filtered
 Sheet1.Range("S7").Value = lstLookup.Value

[COLOR="#006400"] 'run advanced filter[/COLOR]
 [COLOR="#FF0000"]Adv[/COLOR]

 'get the selected value from the listbox
 For I = 0 To Me.lstLookup.ListCount - 1
 If Me.lstLookup.Selected(I) = True Then
 fullName = lstLookup.List(I, 0)
 End If

 Next I

 'find the name
 Set findvalue = Sheet1.Range("G:G").Find(What:=fullName, LookIn:=xlValues).Offset(0, -3)

 'add the database values to the userform
 cNum = 9
 For X = 1 To cNum
 Me.Controls("Reg" & X).Value = findvalue
 Set findvalue = findvalue.Offset(0, 1)
 Next

 errHandler::
 MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
 & Err.Number & vbCrLf & Err.Description & vbCrLf & _
 "Please notify the administrator"

 End Sub

 

[royUK]

This free UserForm shows how to click on a ListBox and populate the TextBoxes

DataBaseForm