• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform populated with data from multiple worksheets

TomNR

TomNR

Member
Hi all,

I am looking for some guidance.

I basically have a Userform where you search for a member of staff, which pulls all of the tasks assigned to that specific staffs (across four Worksheets) into a List Box (I have this working)

But when you click on one of the actions in the list box it is supposed to populate the Userform.

However, I am running into an issue with it populating the User form with the data from the multiple worksheets. I have tried For Each ws In ThisWorkbook.Worksheets but this isn't resolving the issue?

Any help would be appreciated!
Code: 
Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
    Dim cUID As String
    Dim I As Integer
    Dim findvalue
    Dim ws As Worksheet

    'get the select value from the listbox
    For I = 0 To lstLookup.ListCount - 1
        If lstLookup.Selected(I) = True Then
            cUID = lstLookup.List(I, 0)
        End If
    Next I
 
    For Each ws In ThisWorkbook.Worksheets
        Set findvalue = ws.Range("D:D").Find(What:=cUID, LookIn:=xlValues).Offset(0, -3)
    Next ws

    'add the database values to the userform
    cNum = 8
    For X = 1 To cNum
        Me.Controls("reg" & X).Value = findvalue
        Set findvalue = findvalue.Offset(0, 3)
    Next
 
    'disable adding
    Me.cmdAdd.Enabled = False
 
    Exit Sub

End Sub

Mod Edit: Code Tags added
 
Hi Tom.

This is just taking a guess, but there were a couple For loops where I think you want to exit the for after you've found the value of interest. Take a look at this, perhaps it will at least point you in the right direction?
Code: 
Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim cUID As String
Dim I As Integer
Dim findValue As Range
Dim ws As Worksheet

'get the select value from the listbox
For I = 0 To lstLookup.ListCount - 1
    If lstLookup.Selected(I) = True Then
        cUID = lstLookup.List(I, 0)
        'assuming that once we find our cUID, we can stop looking...
        Exit For
    End If
Next I

For Each ws In ThisWorkbook.Worksheets
    'Two points. Are you guaranteed to find the cUID on every sheet? If not, this
    'will cause an error when it tries to do the Offset if it's not found
    'Next, what should happen when you find a value? keep searching sheets?
   
    'Proposed change
    Set findValue = ws.Range("D:D").Find(What:=cUID, LookIn:=xlValues)
    If Not findValue Is Nothing Then
        Set findValue = findValue.Offset(0, -3)
        Exit For
    End If
   
    'Old code
    'Set findValue = ws.Range("D:D").Find(What:=cUID, LookIn:=xlValues).Offset(0, -3)
Next ws

'add the database values to the userform
cNum = 8
For X = 1 To cNum
    Me.Controls("reg" & X).Value = findValue
    Set findValue = findValue.Offset(0, 3)
Next

'disable adding
Me.cmdAdd.Enabled = False

End Sub
 
Luke M said:
Hi Tom.

This is just taking a guess, but there were a couple For loops where I think you want to exit the for after you've found the value of interest. Take a look at this, perhaps it will at least point you in the right direction?
Code: 
Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim cUID As String
Dim I As Integer
Dim findValue As Range
Dim ws As Worksheet

'get the select value from the listbox
For I = 0 To lstLookup.ListCount - 1
    If lstLookup.Selected(I) = True Then
        cUID = lstLookup.List(I, 0)
        'assuming that once we find our cUID, we can stop looking...
        Exit For
    End If
Next I

For Each ws In ThisWorkbook.Worksheets
    'Two points. Are you guaranteed to find the cUID on every sheet? If not, this
    'will cause an error when it tries to do the Offset if it's not found
    'Next, what should happen when you find a value? keep searching sheets?
  
    'Proposed change
    Set findValue = ws.Range("D:D").Find(What:=cUID, LookIn:=xlValues)
    If Not findValue Is Nothing Then
        Set findValue = findValue.Offset(0, -3)
        Exit For
    End If
  
    'Old code
    'Set findValue = ws.Range("D:D").Find(What:=cUID, LookIn:=xlValues).Offset(0, -3)
Next ws

'add the database values to the userform
cNum = 8
For X = 1 To cNum
    Me.Controls("reg" & X).Value = findValue
    Set findValue = findValue.Offset(0, 3)
Next

'disable adding
Me.cmdAdd.Enabled = False

End Sub
Click to expand...

Absolutely awesome! Thank you, that has sorted it out perfectly!

Now I am having an issue where I double click on some of the values in the List Box and they aren't being populated in the userform...

There is no Debug either so I dont know what the issue is here, have you seen anything like this before?

Thank you again for the help!!!
 
Hi Tom. I'd suggest stepping through the code. In code block above, put the line stop near beginning, like so:
Code: 
Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim cUID As String
Dim I As Integer
Dim findValue As Range
Dim ws As Worksheet

Stop

'.....rest of code

That'll force programming to wait there when you double-click. Then you can hit F8 to step through the code 1 line at a time, and hopefully see where the issue is. W/o seeing the actual userform and setup, I don't really have a good idea what the problem could be.
 
Luke M said:
Hi Tom. I'd suggest stepping through the code. In code block above, put the line stop near beginning, like so:
Code: 
Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim cUID As String
Dim I As Integer
Dim findValue As Range
Dim ws As Worksheet

Stop

'.....rest of code

That'll force programming to wait there when you double-click. Then you can hit F8 to step through the code 1 line at a time, and hopefully see where the issue is. W/o seeing the actual userform and setup, I don't really have a good idea what the problem could be.
Click to expand...

Thanks for the tip Luke! Although it doesnt seem to work as it just Debugs when it hits the 'Stop'.

The code for the Lookup that grabs the values is this:

Code: 
Sub Lookup()
    'declare the variables
    Dim rngFind As Range
    Dim strFirstFind As String
  
    'error statement
    On Error GoTo errHandler:
  
    'clear the listbox
    lstLookup.Clear
  
    'look up parts or all of full mname
    With Sheet2.Range("D:D")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, -3)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, -1)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
  
    With Sheet3.Range("D:D")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, -3)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, -1)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
  
    With Sheet5.Range("D:D")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, -3)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, -1)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
  
    With Sheet6.Range("D:D")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, -3)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, -1)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With

    'error block
    On Error GoTo 0
  
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub
So between this and the code in the original post this populates the Userform.

Hope you can see some issue in here :)

Thanks in advance!!
 
Last edited by a moderator:
The Stop command is just there to let you get into Debug mode. Once you're there, should see the yellow highlighting. Then, press F8 to step through the code. You can follow along and see what the code is doing, make sure it's reading variables correctly, exiting for loops, etc.
 
Luke M said:
The Stop command is just there to let you get into Debug mode. Once you're there, should see the yellow highlighting. Then, press F8 to step through the code. You can follow along and see what the code is doing, make sure it's reading variables correctly, exiting for loops, etc.
Click to expand...
Thanks Luke!

Great tip for future Debugging!! Now to see if I can resolve the above...
 
You must log in or register to reply here.
Back
Top