Middle name in Msgbox VBA

[ORoxo]

Hey, guys
I'm having a VBA exam next Monday and I want to be as prepared as I can. In class, my teacher taught us how to get a msg box with first name and last name but we didn't learn how to extract middle names. I tried to ask him how to do so but he wasn't quite explanatory.
In class what we've learn was

Private Sub CommandButton8_Click()
    Dim T As String, FN As String, LN As String
    T = InputBox("Please type your full name")
    FN = MsgBox("Your first name is " & Left(T, InStr(T, " ")))
    LN = MsgBox("Your last name is " & Right(T, Len(T) - InStrRev(T, " ")))
End Sub

I was wondering if there's any way to present the middle name to the user - and, if so, how.

Let's say the user types "Wayna Nanook Aputsiaq Nanuk Berg" and I want to extract:

1) "Aputsiaq"
2) "Nanook"

Thanks,
ORoxo

 

[JoeMo]

This will extract all parts of the name entered provided the user enters the name using space delimiters (like "Wayna Nanook Aputsiaq Nanuk Berg").

Sub ReturnNames()
    Dim T As String, N As Variant, i As Long
    T = InputBox("Please type your full name")
    N = Split(T, " ")
    For i = LBound(N) To UBound(N)
        MsgBox "Part " & i + 1 & " of your name is " & N(i)
    Next i
End Sub

 

[ORoxo]

This will extract all parts of the name entered provided the user enters the name using space delimiters (like "Wayna Nanook Aputsiaq Nanuk Berg").

Sub ReturnNames()
    Dim T As String, N As Variant, i As Long
    T = InputBox("Please type your full name")
    N = Split(T, " ")
    For i = LBound(N) To UBound(N)
        MsgBox "Part " & i + 1 & " of your name is " & N(i)
    Next i
End Sub

That's perfect, JoeMo. I had no idea you could do that!

What if he specifically asks for the 3rd name though, for instance?

 

[Kenneth Hobson]

Private Sub CommandButton8_Click()
  Dim T As String, FN As String, LN As String, MD As String
  Dim rc, a, i As Integer, MN As String
  
  'Wayna Nanook Aputsiaq Nanuk Berg
  T = InputBox("Please type your full name")
  If T = "" Then Exit Sub
  
  a = Split(T, " ")
  FN = a(0)
  LN = a(UBound(a))
  rc = MsgBox("Your first name is " & FN & ".")
    If UBound(a) > 1 Then
    MN = Mid(T, Len(FN) + 1, Len(T) - Len(FN) - Len(LN) - 2)
    rc = MsgBox("Your middle name is possibly " & MN & ".")
  End If
  rc = MsgBox("Your last name is " & LN & ".")
End Sub

 

[JoeMo]

That's perfect, JoeMo. I had no idea you could do that!

What if he specifically asks for the 3rd name though, for instance?

You are welcome.

Since the array N is zero-based, the 3rd name would be N(2).

 

[ORoxo]

Private Sub CommandButton8_Click()
  Dim T As String, FN As String, LN As String, MD As String
  Dim rc, a, i As Integer, MN As String
  
  'Wayna Nanook Aputsiaq Nanuk Berg
  T = InputBox("Please type your full name")
  If T = "" Then Exit Sub
  
  a = Split(T, " ")
  FN = a(0)
  LN = a(UBound(a))
  rc = MsgBox("Your first name is " & FN & ".")
    If UBound(a) > 1 Then
    MN = Mid(T, Len(FN) + 1, Len(T) - Len(FN) - Len(LN) - 2)
    rc = MsgBox("Your middle name is possibly " & MN & ".")
  End If
  rc = MsgBox("Your last name is " & LN & ".")
End Sub

I appreciate your help, Kenneth. However, that is too complex based on what I have learned so far. Therefore, not really useful. Nonetheless, thanks for it.

You are welcome.

Since the array N is zero-based, the 3rd name would be N(2).

JoeMo, adapted the code a little bit and now it's perfect

Private Sub CommandButton2_Click()    Dim T As String, N As Variant
    T = InputBox("Please type your full name")
    N = Split(T, " ")
        MsgBox ("Your third name is " & N(3))
End Sub

Thanks once again for your help. This split function is great!

 

[JoeMo]

JoeMo, adapted the code a little bit and now it's perfect

Private Sub CommandButton2_Click()    Dim T As String, N As Variant
    T = InputBox("Please type your full name")
    N = Split(T, " ")
        MsgBox ("Your third name is " & N([B][COLOR=#ff0000]3[/COLOR][/B]))
End Sub

Thanks once again for your help. This split function is great!

You are welcome, but caution, read my post #5 again. N is a zero-based array, if you want the third name that would be N(2). N(0) is the 1st name, N(1) the 2nd and so on.

 

[ORoxo]

You are welcome, but caution, read my post #5 again. N is a zero-based array, if you want the third name that would be N(2). N(0) is the 1st name, N(1) the 2nd and so on.

Yeah, I tried out the code and realized how it worked. Once again thanks for your help! Really helped a lot.

Have a great weekend!