Public Function ListSelector(TextToSearch As String, lst As ListBox)
On Error GoTo Err_Selector
'*****************************************************************
'* Argument(required/optional) Basic Description *
'*******************************************************************
' TextToSearch (required) Text that we are going to find a Match inside of the Listbox
' lst(required) This is the LISTBOX we are searching through in looking for TextToSearch
'*****************************************************************
'* Variables Variable Definitions *
'*******************************************************************
Dim iColumn As Integer ' Variable used to Run through the various Columns in the Listbox
Dim iRow As Integer ' Variable used to Run through the various Rows in the Listbox
Dim iWord As Integer ' Variable used to Run through the various Words in the Cells of the Listbox
Dim sCell As String ' Variable used to point to the Cell being checked
Dim iCellSize As Integer ' Variable used to Size the Cell Contents being checked
Dim iWordDig As Integer ' Variable used to Choose how far in to the Cell Contents we are going to dig
Dim iTextSize As Integer ' Variable used to determine how many characters are in the Text to Search
'*****************************************************************
' The First thing we need to do is find out how many 'CHARACTERS' there are in the TextToSearch Variable.
' For Example:
' The Word "HOSPITAL" has 8 Letters... or CHARACTERS in it...
' SO, if TextToSearch contains the string 'HOSPITAL' then Len(TextToSearch) will = 8
iTextSize = Len(TextToSearch)
' Then, if our TextToSearch is Empty (or it EQUALS ZERO)...
' Then we are finished and we can EXIT this Function
If iTextSize = 0 Then GoTo Exit_Selector
' Now we want to create a counter.
' First we need to Find out How many COLUMNS (not to be confused with ROWS) that this Listbox has.
' The lst.ColumnCount will count ALL the Columns in this ListBox
' So, Now we create a Counter called iColumn.
' This Counter will count from 1 all the way up to the number of COLUMNS we have in this Listbox.
For iColumn = 0 To lst.ColumnCount - 1
' Again, we do the same thing...
' Here we create a Counter called iRow.
' This Counter will count from 0(ZERO) to the number of ROWS we have in this Listbox MINUS 1
For iRow = 0 To lst.ListCount - 1
' Ok, we are Counting now.
' If the counter JUST started, then iColumn = 1 and iRow = 1
' Now, we are going to create a pointer.
' This pointer will point to a SPECIFIC CELL in our Listbox.
' In our example above, if iColumn =1 and iRow = 1
' Then the SPECIFIC CELL we are pointing at would be in Column 1, Row 1
' So, sCell Equals the Cell that is located at: Column:iColumn / Row:iRow
sCell = lst.Column(iColumn, iRow)
' Now that we have pointed to a Cell, we need to see how many Characters are in this Cell.
iCellSize = Len(sCell)
' With the Size of the Cell now figured out, we need to know how far INTO this cell we want to look.
' If the Entry in this Cell is 10 Characters long... and our iTextSize is only 2...
' (Remember: iTextSize is a number that is equal to how many Characters are in our TextToSearch)
' I will explain in a moment WHY we Subtract iCellSize from iTextSize... For now just understand:
iWordDig = iCellSize - iTextSize
' Ok, again, I will explain in a moment, but we need to make sure that iWordDig is equal to 1 or more.
' So, we have an If/Then statement.
' IF iWordDig is Less than OR Equal to ZERO, then we need to bump it up to equal the number 1
If iWordDig <= 0 Then iWordDig = 1
' ANOTHER Counter.
' This time, we are counting from 1 to WHATEVER NUMBER WE CAME UP WITH FOR iWordDig... see above if you have a question.
For iWord = 1 To iWordDig
' OK... Now I explain.
' See, we are going to take apart each Cell and dig piece by piece for a Match.
' If you will notice the MID statement?
' The Correct Definition for how MID works is this...
' Mid(String, Start As Long,[Length])
' What does this mean?
' Well, when you use Mid... you tell it two to three things.
' First, you give Mid a String.
' We are giving Mid the sCell, remember from above? That is the Entry in the Cell?
' Ok, we give Mid the sCell... what next? We next we tell it WHAT CHARACTER NUMBER...
' If you take sCell, and you number each CHARACTER in it, then "Start As Long" is the Character we will start with.
' SO, if sCell is equal to "HOSPITAL" and we tell it to "Start As Long" with a 4...
' Then that Means we want to start at CHARACTER NUMBER 4 INSIDE HOSPITAL... which is the Letter "P"
' The Last Part of Mid is [Length]. This is actually an Optional point.
' We COULD use Mid without telling it the [Length]. However, We WANT to tell it the Length...
' What IS the Length?
' Ok...
' If we do this like we did the above, lets say we are at the First Time through this counter
' That would Mean iWord = 1
' THIS MEANS:
' 1) We are checking the Contents of sCell
' 2) We are Starting at CHARACTER iWord... or 0(ZERO) for this Example...
' 3) AND we are Grabbing a number of CHARACTERS equal to iTextSize
' THIS is they we Subtracted iCellSize from iTextSize...
' When we dig through through sCell, if it is 5 CHARACTERS Long...
' TWO THINGS CAN HAPPEN:
' 1) FIRST THING:
' the TextToSearch COULD be 6 CHARACTERS Long...
' this would mean that iWordDig would equal a negative number, and therefore...
' Our iWord counter would count from 1 to a negative number which is NOT what we want...
' Which means we would NOT EVEN CHECK sCell AT ALL...
' 2) SECOND THING:
' the TextToSearch COULD be JUST as Long as sCell
' and the SAME thing would happen... we would SKIP it cause iWordDig would equal 0(ZERO)
' So, we have to check it first.
' Ok, now that we have checked sCell... at Character iWord...
' And we have Grabbed iTextSize many CHARACTERS...
' We now check to see if it equals TextToSearch...
If Mid(sCell, iWord, iTextSize) = TextToSearch Then
' If it does...? GREAT! WE HAVE FOUND A MATCH!
' IF we found a Match, we will Grab our Listbox with the lst
' THEN we HIGHLIGHT a Row INSIDE the Listbox with Selected
' The ROW we Highlight is Row number iRow
' After that, we END the Function.
lst.Selected(iRow) = True: GoTo Exit_Selector
' IF what we grabbed with Mid matched, then our IF/THEN happened...
' Now if we HADN'T used the (Goto Exit_Selector) then we would NEED "End If"
' As it is, we are using End If just in case something goes wrong.
End If
' Ok, the Command (Next iWord) means...
' Run the Counter, but BUMP iWord up ONE MORE NUMBER...
' AND Lets go up and start Executing Code at the For iWord = blah blah blah
' So we will NOT Execute (Next iRow) until we reach the END of iWord...
Next iWord
' Same thing for iRow as for iWord...
Next iRow
' AND SAME THING FOR iColumn...
Next iColumn
' Ok, we create a SubRoutine in our Function and we call it: Exit_Selector
Exit_Selector:
' We assign ONE Command to it... (Exit Function)
' When this SubRoutine Happens... This Function is DONE
Exit Function
' Now we need another SubRoutine in our Function as we call IT: Err_Selector
Err_Selector:
' We assign TWO Commands to IT...
' errMessage Err is a Custom Built Function I use to handle Errors...
' If you want you can use the following...
' MsgBox Err.Description
errMessage Err
' NOW we Resume on to Exit_Selector which ends the Function...
' WHY? Because we had an error and there is no reason to continue.
Resume Exit_Selector
' If you have ANY QUESTIONS... Feel free to email me at: [email]randomblink@yahoo.com[/email]
' Started by Fornatian @ [url]www.access-programmers.co.uk[/url]
' See the thread: [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=56992[/url]
' Finished by randomblink...
End Function
