Extract first and last name from cell with two names or cell with double last names

[nicnad]

Hi,

I have some code to extract first and last names from a cell when double clicked and open an internet website and fill a form:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
 
    Cancel = True
    Dim myRangedoc As Range
    Set myRangedoc = Range("phonebookname")
    If Not Intersect(Target, myRangedoc) Is Nothing Then
    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim stGotIt As String
    firstname = Left(Target.Value, InStr(1, Target.Value, " ", vbTextCompare))
    stGotIt = StrReverse(Target.Value)
    stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))
    lastname = StrReverse(Trim(stGotIt))
 
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    IE.Visible = False
 
    ' Send the form data To URL As POST binary request
    IE.Navigate "http://mywebsite.com"
 
    ' Statusbar
    Application.StatusBar = "Loading. Please wait..."
 
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
  
    Application.StatusBar = "Search form submission. Please wait..."
    Set objCollection = IE.document.getElementsByTagName("input")
 
    i = 0
    While i < objCollection.Length
 
        If objCollection(i).Name = "searchForm.lastName" Then
 
            ' Set text for search
            objCollection(i).Value = lastname
 
        ElseIf objCollection(i).Name = "searchForm.firstName" Then
 
            ' Set text for search
            objCollection(i).Value = firstname
 
        Else
            If objCollection(i).Type = "submit" Then
 
                ' "Search" button is found
                Set objElement = objCollection(i)
 
            End If
        End If
        i = i + 1
    Wend
    objElement.Click    ' click button to search
 
    ' Wait while IE re-loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
 
    ' Show IE
    IE.Visible = True
 
    ' Clean up
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
 
    Application.StatusBar = ""
End If
End Sub

This code works great for simple name as "Barrack Obama" in a cell.

The thing is I sometimes have two names in a cell separated by a backslash (i.e. "Barrack Obama / Ben Bernanke")
I also have errors when there is a double last name (i.e. "George W. Bush")
When there is two names in a cell, I would like to have a listbox pop up and have the user choose the name he wants to search on the website and then I would like the variable firstname and lastname to be the one selected by the user.
When there is more than one last name I would like the variable lastname to be both last names (i.e. "W. Bush").

Can you please help me?

Thank you.

 

[nicnad]

Bump...