Trying to reduce repeating code

keezymeezy

New Member
Joined
Jun 17, 2014
Messages
13
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.

view

view


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

Code:
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.

Code:
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Are your listboxes single or multi select?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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.

Code:
' 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case you can do it like
Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   Call transferText(Me.ListBox1.Value, Me.TextBox1)
End Sub
Code:
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

New Member
Joined
Jun 17, 2014
Messages
13

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2014
Messages
13
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,060
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top