PDA

View Full Version : manipulating userform objects from a code module



tpoynton
01-10-2009, 12:24 PM
Greetings - had a tough time with the name of the thread...

I'm trying to clean up my code, and I have this little bit of code currently on a bunch of different userforms. I thought I'd put the code into a module, and just call the sub from each userform. It is harder than I thought (for me), as I have not found a way to make it work.

here is the code on the userform to call the code:
Call FindName(Me.TextBox_FindName, Me.ListBox_Name)
here is the code in the module. doesnt work; I've tried some variations of variables to get the name of the userform from the (string, userform, in addition to those listed), but can not seem to work it out.
Public Sub FindName(sCallingTbox As TextBox, sCallingLbox As ListBox)
'this is a psuedo autocomplete function
Dim txt As String
Dim i As Long
txt = sCallingTbox.Value & "*"
With sCallingLbox 'used to deal with multiselect being on
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
For i = 0 To .ListCount - 1
If txt <> "*" Then
If .List(i) Like txt Then
.Selected(i) = True
Exit For
End If
End If
Next i
End With
End Sub

I'm pretty sure there is something simple I am overlooking, but I have not been able to figure this out for a while!

Thanks, tim

Bob Phillips
01-10-2009, 02:31 PM
Tim,

There are two types of textbox, and the worksheet type is higher in the pecking order, so when you want the forms type you have to say so



Public Sub FindName(sCallingTbox As MSForms.TextBox, sCallingLbox As MSForms.ListBox)
'this is a psuedo autocomplete function
Dim txt As String
Dim i As Long
txt = sCallingTbox.Value & "*"
With sCallingLbox 'used to deal with multiselect being on
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
For i = 0 To .ListCount - 1
If txt <> "*" Then
If .List(i) Like txt Then
.Selected(i) = True
Exit For
End If
End If
Next i
End With
End Sub

mikerickson
01-10-2009, 04:05 PM
Public Sub FindName(sCallingTbox As MSForms.TextBox, sCallingLbox As MSForms.ListBox)
'this is a psuedo autocomplete function
Dim txt As String
Dim i As Long
If 0 < Len(sCallingTbox.Text) Then
txt = sCallingTbox.Value & "*"
With sCallingLbox 'used to deal with multiselect being on
For i = 0 To .ListCount - 1
.Selected(i) = (.List(i) Like txt)
'txt = IIf(.Selected(i), Chr(5), txt):Rem use to make first found the only found
Next i
End With
End If
End Sub

tpoynton
01-10-2009, 06:06 PM
Bob, thank you for explaining - I always appreciate it!

Mike, thanks for the code - I couldnt figure out your commented code, so what you provided might work better. the code you provided selected all entries that matched the criteria. I had since decided to not select values in multiselect listboxes, but just put focus on them.

my first go-round was not great; this does more of what I want now, and works perfectly. If it wasnt transparent from the original post, this allows the user to begin typing into a textbox to quickly scroll through a listbox. I know you can do this somewhat by just typing into the listbox, but this seems to help some users...

since sometimes the listboxes this will run into use multiselect, and other times not, this simply brings the matching entry to the top for multiselect listboxes, and selects the entry in single select listboxes.


Public Sub FindName(msfCallingTbox As MSForms.TextBox, msfCallingLbox As MSForms.ListBox)
'this is a psuedo autocomplete function
Dim txt As String
Dim i As Long
With msfCallingLbox
If 0 < Len(msfCallingTbox.Text) Then
txt = msfCallingTbox.Value & "*"
For i = 0 To .ListCount - 1
If .List(i) Like txt Then
If msfCallingLbox.MultiSelect = fmMultiSelectSingle Then
.Selected(i) = True
Exit For
Else
.TopIndex = i
Exit For
End If
End If
Next i
Else
.TopIndex = 0
.Selected(.ListIndex) = False
End If
End With
End Sub


Thank you, again, I really appreciate it! This was one of the few times I could not find an answer by searching the forums/google.

Bob Phillips
01-11-2009, 05:54 AM
If it wasnt transparent from the original post, this allows the user to begin typing into a textbox to quickly scroll through a listbox. I know you can do this somewhat by just typing into the listbox, but this seems to help some users...

I started by thinking that I could improve your code, but then I realised what you were doing, so I just made the change to make it work.