Trying to reduce repeating code

[keezymeezy]

I have a userform with three textboxes that collect a search term: search1, search 2, search3. The search value runs through a function generating a list which populates listboxes: listbox1, listbox2, listbox3. If an item in the listbox is doubleclicked then it runs an Instr function selecting only the text I need and copies it to a textbox: result1, result2, result3.

I have the following code which is just repeating itself with the different but related names

Private Sub ListBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
With searchForm.ListBox1
   
         For c = 0 To .ListCount - 1
        
            If ListBox1.Selected(c) = True Then
            
                    If InStr(1, ListBox1.List(c), ".") <> 0 Then
                    
                        searchForm.search1.Value = Left(ListBox1.List(c), InStr(1, ListBox1.List(c), ".") + 1)
'
                    Else
                    
                        searchForm.search1.Value = Left(ListBox1.List(c), InStr(1, ListBox1.List(c), " ") - 1)
                        
                        
                    End If
                    
                    lenItem = Len(ListBox1.List(c)) 'length of the item
                    pos1space = InStr(1, ListBox1.List(c), " ") 'position of the first space
                   
                   
            Exit For
            End If
        Next
    End With
End Sub


Private Sub ListBox2_DblClick(ByVal cancel As MSForms.ReturnBoolean)
With searchForm.ListBox2
   
    
        For c = 0 To .ListCount - 1
        
            If ListBox2.Selected(c) = True Then
            
                    If InStr(1, ListBox2.List(c), ".") <> 0 Then
                    
                        searchForm.search2.Value = Left(ListBox2.List(c), InStr(1, ListBox2.List(c), ".") + 1)
'
                    Else
                    
                        searchForm.search2.Value = Left(ListBox2.List(c), InStr(1, ListBox2.List(c), " ") - 1)
                        
                        
                    End If
                    
                    lenItem = Len(ListBox2.List(c)) 'length of the item
                    pos1space = InStr(1, ListBox2.List(c), " ") 'position of the first space
                    
                   
                   
            Exit For
            End If
            
        Next
        
    End With
End Sub


Private Sub ListBox3_DblClick(ByVal cancel As MSForms.ReturnBoolean)
With searchForm.ListBox3
   
    
        For c = 0 To .ListCount - 1
        
            If ListBox3.Selected(c) = True Then
            
                    If InStr(1, ListBox3.List(c), ".") <> 0 Then
                    
                        searchForm.search3.Value = Left(ListBox3.List(c), InStr(1, ListBox3.List(c), ".") + 1)
'
                    Else
                    
                        searchForm.search3.Value = Left(ListBox3.List(c), InStr(1, ListBox3.List(c), " ") - 1)
                        
                        
                    End If
                    
                    lenItem = Len(ListBox3.List(c)) 'length of the item
                    pos1space = InStr(1, ListBox3.List(c), " ") 'position of the first space
                   
                   
            Exit For
            End If
            
        Next
        
    End With


End Sub

The above code works. What I'm trying to do is condense this code so it isn't so repetitive. I was thinking of something like this (which doesn't seem to work.

Dim lb as String
Dim sch as String

Private Sub ListBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     lb = ListBox1
     sch = Search1
     transferText (lb, sch)
End Sub

Private Sub ListBox2_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     lb = ListBox2
     sch = Search2
     transferText (lb, sch)
End Sub

Private Sub ListBox3_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     lb = ListBox3
     sch = Search3
     transferText (lb, sch)
End Sub

Private Sub transferText(lb, sch)
With searchForm.lb
   
         For c = 0 To .ListCount - 1
        
            If lb.Selected(c) = True Then
            
                    If InStr(1, lb.List(c), ".") <> 0 Then
                    
                        searchForm.sch.Value = Left(lb.List(c), InStr(1, lb.List(c), ".") + 1)
'
                    Else
                    
                        searchForm.sch.Value = Left(lb.List(c), InStr(1, lb.List(c), " ") - 1)
                        
                        
                    End If
                    
                    lenItem = Len(lb.List(c)) 'length of the item
                    pos1space = InStr(1, lb.List(c), " ") 'position of the first space
                   
                   
            Exit For
            End If
        Next
    End With

End Sub

It fails with "Compile Error. Method or data member not found" and highlights the first occurrence of 'lb' in Private Sub transferText(lb, sch).
Can anyone help me?

 

[Fluff]

Are your listboxes single or multi select?

 

[keezymeezy]

single select

 

[mikerickson]

You have lb declared as a String module wide variable.

But it looks like TransferText uses lb as an argument. Although you don't specify, it looks like it is a Listbox object.

Try this.
Note that the lb and sch arguments are object variables (arguments), not properties of a userform, so they are not qualified.

' Dim lb as String   remove this <<<<<<<<
' Dim sch as String remove this <<<<<<<<

Private Sub ListBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     transferText  searchForm.ListBox1 , searchForm.search1
End Sub

Private Sub ListBox2_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     transferText  searchForm.ListBox2 , searchForm.search2
End Sub


Private Sub ListBox3_DblClick(ByVal cancel As MSForms.ReturnBoolean)
     transferText  searchForm.ListBox3 , searchForm.search3
End Sub

Private Sub transferText(lb As MsForms.ListBox, sch As MsForms.TextBox)
With lb
   
         For c = 0 To .ListCount - 1
        
            If .Selected(c) = True Then
            
                    If InStr(1, .List(c), ".") <> 0 Then
                    
                        sch.Value = Left(.List(c), InStr(1, .List(c), ".") + 1)
'
                    Else
                    
                        sch.Value = Left(.List(c), InStr(1, .List(c), " ") - 1)
                        
                        
                    End If
                    
                    lenItem = Len(.List(c)) 'length of the item
                    pos1space = InStr(1, .List(c), " ") 'position of the first space
                   
                   
            Exit For
            End If
        Next
    End With

End Sub

 

[Fluff]

In that case you can do it like

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   Call transferText(Me.ListBox1.Value, Me.TextBox1)
End Sub

Private Sub transferText(LbValu As String, Sch As MSForms.TextBox)
   Dim i As Long
   
   i = InStr(1, LbValu, ".")
   If i > 0 Then
      Sch.Value = Left(LbValu, i + 1)
   Else
      Sch.Value = Left(LbValu, InStr(1, LbValu, " ") - 1)
   End If
'   lenItem = Len(lb.List(c)) 'length of the item
'   pos1space = InStr(1, lb.List(c), " ") 'position of the first space
End Sub

 

[keezymeezy]

@mikerickson Brilliant, that worked perfectly. Thank you so much!

 

[keezymeezy]

@Fluff That works perfectly as well. And you have shortened my code! Thank you -- i will study how that works.

 

[Fluff]

As you are using single select listboxes, there is no need to loop through the listbox to see what has been selected, you can simply return the the value of the selected item.
Probably won't be any difference in speed, unless you have thousands of values in the listbox.

 

[keezymeezy]

As you are using single select listboxes, there is no need to loop through the listbox to see what has been selected, you can simply return the the value of the selected item.
Probably won't be any difference in speed, unless you have thousands of values in the listbox.

Thanks! I'll keep that in mind for when I use multiselect

 

[Fluff]

My pleasure