Useful for certain type of applications (eg for names and addresses etc), being able to Title Caps (ie capitalise first letter of each word) is achieved with the following function. This code also allows you to pass names like "mcDonald", and have the correct name "McDonald" returned.

Code

Dim strString as String
strString = "joe bloggs"
strString = MakeProperCase(strString)

strString will now equal "Joe Bloggs"

MakeProperCase Function

Function MakeProperCase(ByVal StringIn As String) As String
    Dim BlankSpace As Long
    StringIn = LCase$(Trim$(StringIn))
    Do
       Mid$(StringIn, BlankSpace + 1, 1) = _
                 UCase$(Mid$(StringIn, BlankSpace + 1, 1))
        BlankSpace = InStr(BlankSpace + 1, StringIn, " ")
    Loop While BlankSpace
    MakeProperCase = StringIn
End Function

Addition

This is just an addition to your Make Title Case code. Since many people are using newer versions of VB that support the Split() function, I suggest adding the following to your example for those people:

Private Function TitleCaps(inString As String) As String
 tmpString = Split(inString, " ")
 maxNum = UBound(tmpString)
  For i = 0 To maxNum
   tmpString(i) = UCase(Left(tmpString(i), 1)) & LCase(Mid(tmpString(i), 2))
   outString = outString & tmpString(i) & " "
  Next i
 TitleCaps = Trim(outString)
End Function

Addition by Bradford Lee Hornor

2nd Addition

Probably the easiest way to convert a string to Title Case is by calling the StrConv-function, like this:
(StrConv is available on VB versions 5.0 and 6.0. It's possible that it may not work on older VB versions)

Debug.Print StrConv(TextString, vbProperCase)

Addition by Rick Rothstein